CODEWITHSUNDEEP
sqlsql-serverpivot
jimtut
jimtut

Reputation: 2393

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 trying to convert, possibly because my SQL knowledge is pretty basic.

My input data is a list of Release Names and Story Points extracted from our JIRA server, along with the extract date. The table I'm trying to use will actually contain extracts from many different projects, although I'm excluding the ProjectName column from these examples.

Input Data:

Version     Date        StoryPoints
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

We need to generate a table in either format below, which will be used to create a "burnup" chart in our Confluence wiki. Since each project can have different Version names, we can't hard-code any of the column names below (although "Completed" will exist in all projects).

Also, even though we will standardize on dates similar to ones in the example, I would prefer to not hard-code any of the date values into the query either.

Output Format #1:

Date        1.0 2.0 3.0 Completed
2017-01-01  10  10  5   0
2017-02-01  10  10  5   5
2017-03-01  15  10  5   15
2017-04-01  15  10  5   28

I recognize that it may be difficult to structure the data that way, so it's possible to use the following output format too, which I can also chart in Confluence (although it's not as intuitive as the above format).

Output Format #2:

Versions    2017-01-01  2017-02-01  2017-03-01  2017-04-01
1.0         10          10          15          15
2.0         10          10          10          10
3.0         5           5           5           5
Completed   0           5           15          28

Any help is GREATLY appreciated!

Upvotes: 0

Views: 186

Answers (2)

Von Abanes
Von Abanes

Reputation: 716

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

Source:

            IF ( OBJECT_ID('tempdb..#TMPtbl') IS NOT NULL )
                BEGIN
                    DROP TABLE #TMPtbl
                END


            CREATE TABLE #TMPtbl
                (
                  Id INT IDENTITY(1, 1)
                         PRIMARY KEY ,
                  [Version] VARCHAR(10) ,
                  [Date] DATETIME ,
                  StoryPoints INT
                )

            INSERT  INTO #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(( SELECT DISTINCT
                                                                    ', '
                                                                    + QUOTENAME(CAST(t.[Version] AS VARCHAR(154)))
                                                           FROM     #TMPtbl t
                                                         FOR
                                                           XML PATH('')
                                                         ), 1, 2, '')


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

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

                DECLARE @StrColSumDate NVARCHAR(MAX) = STUFF(( SELECT DISTINCT
                                                                        ', ' + '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
    GROUP BY pvt.[Date]'



DECLARE @SQL2 NVARCHAR(MAX)  = N'SELECT [Version],
        ' + @StrColSumDate + '
                FROM (
                SELECT * FROM #TMPtbl

        )P
    PIVOT (
            SUM(StoryPoints) FOR dATE IN (' + @StrColDate + ')
    )PVT
    GROUP BY pvt.[Version]'

EXEC(@SQL1)
EXEC(@SQL2)

Results

    Date                    1.0         2.0         3.0         Completed
    ----------------------- ----------- ----------- ----------- -----------
    2017-01-01 00:00:00.000 10          10          5           0
    2017-02-01 00:00:00.000 10          10          5           5
    2017-03-01 00:00:00.000 15          10          5           15
    2017-04-01 00:00:00.000 15          10          5           28

    (4 row(s) affected)


    Version    2017-01-01  2017-02-01  2017-03-01  2017-04-01
    ---------- ----------- ----------- ----------- -----------
    1.0        10          10          15          15
    2.0        10          10          10          10
    3.0        5           5           5           5
    Completed  0           5           15          28

    (4 row(s) affected)

Upvotes: 2

Xingzhou Liu
Xingzhou Liu

Reputation: 1559

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.

Upvotes: 2

Related Questions