SQL 数据透视表函数

Question

请帮我编写一个选择查询，用于如下所示旋转我的表

我的表结构

以及所需的结果结构

StoryLineID PlotID StoryLine Question

 1          1      01      question 1
 1          1      02      question 2
 1          1      03      question 3
 1          1      04      question 4
 1          1      05      question 5

Answer 1

试试这个 -

DECLARE @temp TABLE
(
      ID INT
    , PlotID INT
    , StoryLine_01 VARCHAR(10)
    , StoryLine_02 VARCHAR(10)
    , StoryLine_03 VARCHAR(10)
    , StoryLine_04 VARCHAR(10)
    , StoryLine_05 VARCHAR(10)
)

INSERT INTO @temp
VALUES 
    (1, 3, 'Q1', 'Q2', 'Q3', 'Q4', 'Q5'),
    (2, 4, 'Q1', 'Q2', 'Q3', 'Q4', 'Q5')

SELECT 
      ID
    , PlotID
    , StoryLine = RIGHT(StoryLine, 2)
    , Question
FROM @temp
UNPIVOT 
(
    Question FOR StoryLine IN (
        StoryLine_01, StoryLine_02, 
        StoryLine_03, StoryLine_04, 
        StoryLine_05
    )
) unpvt

更新(列的动态计数):

IF OBJECT_ID (N'dbo.temp') IS NOT NULL
   DROP TABLE dbo.temp

CREATE TABLE dbo.temp
(
      ID INT
    , PlotID INT              , StoryLine_01 VARCHAR(10)
    , StoryLine_02 VARCHAR(10), StoryLine_03 VARCHAR(10)
    , StoryLine_04 VARCHAR(10), StoryLine_05 VARCHAR(10)
)

INSERT INTO dbo.temp
VALUES (1, 3, 'Q1', 'Q2', 'Q3', 'Q4', 'Q5'), (2, 4, 'Q1', 'Q2', 'Q3', 'Q4', 'Q5')

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
    SELECT 
          ID
        , PlotID
        , StoryLine = RIGHT(StoryLine, 2)
        , Question
    FROM dbo.temp
    UNPIVOT (
        Question FOR StoryLine IN (
            ' + STUFF((
                SELECT ', ' + name
                FROM sys.columns WITH(NOWAIT)
                WHERE [object_id] = OBJECT_ID('dbo.temp')
                    AND name LIKE 'StoryLine_%'
                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') + 
        ')
    ) unpvt'

EXEC sys.sp_executesql @SQL

输出-

ID          PlotID      StoryLine Question
----------- ----------- --------- ----------
1           3           01        Q1
1           3           02        Q2
1           3           03        Q3
1           3           04        Q4
1           3           05        Q5
2           4           01        Q1
2           4           02        Q2
2           4           03        Q3
2           4           04        Q4
2           4           05        Q5