如何在SQL中将行转换为列

I've been referencing this question a lot, but my case is a little different so I haven't quite figured it out.

我一直在引用这个问题，但我的情况有点不同所以我还没弄清楚。

I have a set of data that looks something like this:

我有一组看起来像这样的数据：

    --------------------------------------
    | Id | Answer|  Question   | EntryBy
    --------------------------------------
    | 1  |John   |Name?        | User1  |
    | 2  |2.4    |Raiting?     | User1  |
    | 3  |ZH1E4A |UserId?      | User1  |
    | 4  |Paul   |Name?        | User1  |
    | 5  |2.3    |Raiting?     | User1  |
    | 6  |Ron    |Name?        | User2  |
    | 7  |857685 |UserId?      | User2  |
    ----------------------------

I need to pivot the data so that it's structured like so:

我需要透视数据，使其结构如下：

    ----------------------------------------------------------
    | Category | Name? | Raiting? | UserId? | EntryBy |
    ----------------------------------------------------------
    | Category1| John  |   2.4    | ZH1E4A  | User1   |
    | Category1| Paul  |   2.3    |  NULL   | User1   |
    | Category1| Ron   |   NULL   |  857685 | User2   |

As you can see, there are multiple "Questions" but they don't always have an answer/value. I know the exact number of questions that may be asked/answered so I'm assuming that may help if I used a CASE expression?

如您所见，有多个“问题”，但它们并不总是有答案/值。我知道可以提出/回答的问题的确切数量，所以我假设如果我使用CASE表达式可能有帮助吗？

Note: The 'Category' column in the last table is just another value similar to 'EntryBy' in the first. I've attempted the pivot approach in the cited question, but the results I get are not correct. I also tried the CASE statement but it resulted in an error since the Questions are titled the same.

注意：最后一个表中的“类别”列只是另一个类似于第一个中的“EntryBy”的值。我在引用的问题中尝试过枢轴方法，但我得到的结果并不正确。我也尝试了CASE语句但由于问题标题相同而导致错误。

2 个解决方案

#1

Being 2008, we lose the sum() over function, but we can simulate it via a cross apply to create a Grp indicator.

在2008年，我们失去sum（）函数，但我们可以通过交叉应用来模拟它来创建一个Grp指标。

This also assumes the ID is sequential (risky) and Name? is the Group Key.

这也假设ID是顺序的（有风险的）和Name？是组密钥。

Also, check the spelling of RAITING

另外，检查RAITING的拼写

Also, I have no idea where Category is coming from

另外，我不知道Category来自哪里

Example

例

Select [Name?]    = max(case when Question = 'Name?'   then Answer end)
      ,[Raiting?] = max(case when Question = 'Raiting?' then Answer end)
      ,[UserId?]  = max(case when Question = 'UserId?' then Answer end)
      ,[EntryBy?] = max([EntryBy])
 From (
        Select A.*
              ,B.Grp
         From YourTable A
         Cross Apply (Select Grp=count(*) from YourTable where Question='Name?' and ID<=A.ID) B
      ) A
 Group By Grp

Returns

Name?       Raiting?    UserId?  EntryBy?
John        2.4         ZH1E4A   User1
Paul        2.3         NULL     User1
Ron         NULL        857685   User2

#2

This only does a single parse of the table (or "Values Table Expression") for this one, compared to John's, which does 2:

这只是对这个表的单个解析（或“值表表达式”），与John的相比，它执行2：

WITH VTE AS (
    SELECT *
    FROM (VALUES
                (1,'John  ','Name?   ','User1'),
                (2,'2.4   ','Raiting?','User1'),
                (3,'ZH1E4A','UserId? ','User1'),
                (4,'Paul  ','Name?   ','User1'),
                (5,'2.3   ','Raiting?','User1'),
                (6,'Ron   ','Name?   ','User2'),
                (7,'857685','UserId? ','User2'),
                (8,'Steve  ','Name?   ','User3'),
                (9,'2.5   ','Raiting?','User3'),
                (10,'Jane  ','Name?   ','User3'),
                (11,'GA18S1','UserId? ','User3'),
                (12,'2.3   ','Raiting?','User3'),
                (13,'ABH12D','UserId? ','User3')) V(ID, Answer, Question, EntryBy)),
Groups AS(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY ID ASC) -
           ROW_NUMBER() OVER (PARTITION BY CASE WHEN Question = 'Name?' THEN 0 ELSE 1 END ORDER BY ID ASC) AS Grp
    FROM VTE)
SELECT 'Category1' AS Category,
       MAX(CASE Question WHEN 'Name?' THEN Answer ELSE NULL END) AS [Name?],
       MAX(CASE Question WHEN 'Raiting?' THEN Answer ELSE NULL END) AS [Raiting?],
       MAX(CASE Question WHEN 'UserID?' THEN Answer ELSE NULL END) AS [UserID?],
       EntryBy
FROM Groups
GROUP BY CASE Grp WHEN 0 THEN Grp + 1 ELSE Grp END,
         EntryBy
ORDER BY CASE Grp WHEN 0 THEN Grp + 1 ELSE Grp END;

I also added a few extra values to display what happens if the sequencing goes wrong.

我还添加了一些额外的值来显示如果排序错误会发生什么。

Result set:

结果集：

Category  Name?   Raiting? UserID? EntryBy
--------- ------- -------- ------- -------
Category1 John    2.4      ZH1E4A  User1
Category1 Paul    2.3      NULL    User1
Category1 Ron     NULL     857685  User2
Category1 Steve   2.5      NULL    User3
Category1 Jane    2.3      GA18S1  User3

#1