SQL Transpose row data into columns

Question

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!

What you're looking for is a PIVOT, and there are a few different ways you can do this, but if you search for PIVOT SQL Server you should find a bunch of different results that will help you. — ZLK
– ZLK, Commented Jul 19, 2017 at 3:33

Von Abanes · Accepted Answer · 2017-07-19 05:13:06Z

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)

Any way to store the output table from @SQL1 in a temp table, instead of selecting/returning it? I have a related question (stackoverflow.com/questions/45194835) where I need to use some of this data to predict some "future" data, and then UNION it all together. I have the future data working, but can't figure out how to include data from this output (specifically the last values in those dynamically-named columns).
based on the related question, yes its possible to get the last value. you will need to create a temporary table and declare the column ( date, completed ) outside the command query string. E.g. I modifed @SQL1 - drive.google.com/file/d/0B4OjvkDTL97AbDlUUVZuUDUtLTg/view

Xingzhou Liu · Accepted Answer · 2017-07-19 03:43:48Z

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.

edited Jul 19, 2017 at 3:43

answered Jul 19, 2017 at 3:36

Xingzhou Liu

1,5799 silver badges13 bronze badges

1 Comment

Zohar Peled Over a year ago

This is a great comment but a poor answer. While the links you provided might help answering the question, your answer itself lacks content.

Collectives™ on Stack Overflow

SQL Transpose row data into columns

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related