Sql Transpose Row Data Into Columns

I'm looking for a pure-SQL way (SQL Server 2012 if it matters) to convert row data to columns. I've tried searching for this, and can't find anything for the data format I'm tryin

Solution 1:

As suggested by Xingzhou Liu, I came up into this.

Source:

            IF ( OBJECT_ID('tempdb..#TMPtbl') ISNOTNULL )
                BEGINDROPTABLE #TMPtbl
                ENDCREATETABLE #TMPtbl
                (
                  Id INTIDENTITY(1, 1)
                         PRIMARY KEY ,
                  [Version] VARCHAR(10) ,
                  [Date] DATETIME ,
                  StoryPoints INT
                )

            INSERTINTO #TMPtbl
                    ( Version, Date, StoryPoints )
            VALUES  ( '1.0', '2017-01-01', 10 ),
                    ( '1.0', '2017-02-01', 10 ),
                    ( '1.0', '2017-03-01', 15 ),
                    ( '1.0', '2017-04-01', 15 ),
                    ( '2.0', '2017-01-01', 10 ),
                    ( '2.0', '2017-02-01', 10 ),
                    ( '2.0', '2017-03-01', 10 ),
                    ( '2.0', '2017-04-01', 10 ),
                    ( '3.0', '2017-01-01', 5 ),
                    ( '3.0', '2017-02-01', 5 ),
                    ( '3.0', '2017-03-01', 5 ),
                    ( '3.0', '2017-04-01', 5 ),
                    ( 'Completed', '2017-01-01', 0 ),
                    ( 'Completed', '2017-02-01', 5 ),
                    ( 'Completed', '2017-03-01', 15 ),
                    ( 'Completed', '2017-04-01', 28 )

Using STUFF and FOR XML you can get the column dynamically

DECLARE@StrColVer NVARCHAR(MAX) = STUFF(( SELECTDISTINCT', '+ QUOTENAME(CAST(t.[Version] ASVARCHAR(154)))
                                                           FROM     #TMPtbl t
                                                         FOR
                                                           XML PATH('')
                                                         ), 1, 2, '')


                DECLARE@StrColSUmVer NVARCHAR(MAX) = STUFF(( SELECTDISTINCT', '+'SUM('+ QUOTENAME(CAST(t.[Version] ASVARCHAR(154)))
                                                                        +') '+ QUOTENAME(CAST(t.[Version] ASVARCHAR(154)))
                                                              FROM      #TMPtbl t
                                                            FOR
                                                              XML PATH('')
                                                            ), 1, 2, '')

                DECLARE@StrColDate NVARCHAR(MAX) = STUFF(( SELECTDISTINCT', '+ QUOTENAME(FORMAT(t.Date,
                                                                              'yyyy-MM-dd'))
                                                            FROM    #TMPtbl t
                                                          FOR
                                                            XML PATH('')
                                                          ), 1, 2, '')

                DECLARE@StrColSumDate NVARCHAR(MAX) = STUFF(( SELECTDISTINCT', '+'SUM('+ QUOTENAME(FORMAT(t.Date,
                                                                              'yyyy-MM-dd'))
                                                                        +') '+ QUOTENAME(FORMAT(t.Date,
                                                                              'yyyy-MM-dd'))
                                                               FROM     #TMPtbl t
                                                             FOR
                                                               XML PATH('')
                                                             ), 1, 2, '')

Then Pivot and Execute it as a command query string.

DECLARE @SQL1 NVARCHAR(MAX)  = N'SELECT [Date],' + @StrColSUmVer + 'FROM (
                SELECT * FROM #TMPtbl

        )P
    PIVOT (
            SUM(StoryPoints) FOR Version IN (' + @StrColVer + ')
    )PVT
    GROUPBY pvt.[Date]'DECLARE @SQL2 NVARCHAR(MAX)  = N'SELECT [Version],' + @StrColSumDate + 'FROM (
                SELECT * FROM #TMPtbl

        )P
    PIVOT (
            SUM(StoryPoints) FORdATEIN (' + @StrColDate + ')
    )PVT
    GROUPBY pvt.[Version]'

EXEC(@SQL1)
EXEC(@SQL2)

Results

Date1.02.03.0Completed-------------------------------------------------------------------2017-01-01 00:00:00.000 1010502017-02-01 00:00:00.000 1010552017-03-01 00:00:00.000 15105152017-04-01 00:00:00.000 1510528(4row(s)affected)Version2017-01-01  2017-02-01  2017-03-01  2017-04-01------------------------------------------------------1.0101015152.0101010103.05555Completed051528(4row(s)affected)

Solution 2:

SQL Server 2012 requires you to hard code one of the dimensions you are pivoting by into the PIVOT query.

One way you could get around it is by building and executing a dynamic query string.