CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2008-r2sql-server-2012
Pawan
Pawan

Reputation: 1744

Show One Column Data as Two Columns in SQL

I have saved language string resources in a table. These are saved as row wise for each culture code as shown below:

 ---------------------------------------------
  CULTURE      KEYFIELD                 VALUEFIELD
 ----------------------------------------------

   en-US       AadhaarUID              Aadhaar UID
   en-US       Abbreviation            Abbreviation
   en-US       Abbreviation_CD         Abbreviation
   en-US       Abbreviation_DM         Abbreviation
   en-US       AboutPortal             About Portal
   hi-IN       AadhaarUID              आधार यूआईडी नंबर लिखें
   hi-IN       Abbreviation            संक्षिप्त
   hi-IN       Abbreviation_CD         संक्षिप्त
   hi-IN       Abbreviation_DM         संक्षिप्त
   hi-IN       AboutPortal             पोर्टल के बारे में

I want to display the column ValueField as two columns one for english and one for hindi. Number of rows for each culture will be equal.PFFIELD will be only from hi-IN.

Means Output should be as:

   -------------------------------------------------------
     PKFIELD   ENGLISH                         HINDI
    ------------------------------------------------------
        2       Aadhaar UID                 आधार यूआईडी नंबर लिखें
        4       Abbreviation                 संक्षिप्त
        6       Abbreviation                 संक्षिप्त
        8       Abbreviation                 संक्षिप्त
        10       About Portal                  पोर्टल के बारे में

How can I do this in SQL?

Thanks

Upvotes: 3

Views: 2672

Answers (4)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Try this:

SELECT
    PKField = MAX(CASE WHEN Culture = 'hi-IN' THEN PKField END),
    English = MAX(CASE WHEN Culture = 'en-US' THEN ValueField END),
    Hindi = MAX(CASE WHEN Culture = 'hi-IN' THEN ValueField END) 
FROM temp
GROUP BY KeyField

Upvotes: 2

StuartLC
StuartLC

Reputation: 107247

And for good measure, you can also pivot - this will be more useful for larger numbers of languages:

SELECT [KEYFIELD], [en-US], [hi-IN]
FROM Abbreviations
PIVOT
(
  MAX([VALUEFIELD])
  FOR [CULTURE] IN ([en-US], [hi-IN])
) y;

SqlFiddle here

Upvotes: 1

Matt
Matt

Reputation: 15091

select a.valuefield as "ENGLISH", b.valuefield as "HINDI"
from tab a, tab b
where
    a.keyfield = b.keyfield
    and a.culture = 'en-US'
    and b.culture = 'hi-IN'
;

Upvotes: 0

Vishal Gajjar
Vishal Gajjar

Reputation: 1029

SELECT  [English] = E.[ValueFiled]
        ,[Hindi] = H.[ValueFiled]
FROM    [TableName] E
INNER JOIN (SELECT * FROM [TableName] H WHERE H.[Culture] = 'hi-IN') H
        ON E.[KeyField] = H.[KeyField]
WHERE   E.[Culture] = 'en-US'

Upvotes: 0

Related Questions