在SQL中将一列数据显示为两列。

Question

我在表中保存了语言字符串资源。对于每个区域性代码，这些代码按行顺序保存，如下所示：

 ---------------------------------------------
  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             पोर्टल के बारे में

我想将列ValueField显示为两列，一列用于英语，一列用于印地语。每个区域性的行数是相等的。PFFIELD只来自hi-IN。

平均产出应为：

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

我如何在SQL中做到这一点？

谢谢

Answer 1

试试这个：

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

Answer 2

为了更好地衡量，您也可以使用支点--这将对更多的语言更有用：

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

SqlFiddle在这里

Answer 3

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'