SQL Stored procedure, Output rows as columns for summary

Question

Using SQL 2005 Here is what my SQL results look like

UserID | Frame | Choice | Description | DateTime
------------------------------------------------
bcn005 | PL_P03| 1      | Rules-Based | 2011-10-24 14:14:26
bcn005 | PL_P04| 0      | VirtueBased | 2011-10-24 14:16:37
...
bmk172 | Prac_1| 0      | None        | 2011-10-25 12:45:38
...

My client wants the results to look like this

UserID | Frame | Choice | Description | DateTime  | Frame | Choice | Description | DateTime 
-------------------------------------------------------------------------------------------
bcn005 | PL_P03| 1      | Rules-Based | 2011-10-..| PL_P04| 0      | VirtueBased | 2011-10-..
bmk172 | Prac_1| 0      | None        | 2011-10-..|

Where all of a participants responses are on one row. There basically needs to be another set of Frame/Choice Description/DateTime for every row of the result set.

These are survey results, but it is the type of survey where one participant might have went back and changed their answer 5 times. So the amount of Frame/Choice Description/DateTime for each UserID will be different.

I know I should use a stored procedure (or maybe even an excel macro) but I am completely new to both of these. Any guidance is appreciated. Here is my SQL query

select UserID, locationName AS 'Frame', student_response AS 'Choice',
description, DateTime
from tblStudentResponses
WHERE GroupID in (18,20,21,36,37,38,39,40)
AND UserID in (SELECT UserID FROM tblStudentResponses WHERE
locationName = 'Character1Certificate')
AND DateTime > '2011-10-01'
AND type = 'choice'
order by UserID

!! UPDATE !! Full working solution and test code

--Sample Data Preperation DECLARE @FrameChoices as TABLE(UserId varchar(50),Frame varchar(50),Choice varchar(4000),Description varchar(1000),[DateTime] DateTime)

--Test Values
INSERT INTO @FrameChoices(UserId,Frame,Choice,Description,[DateTime])
SELECT  UserId,Frame,Choice,Description,[DateTime]
FROM (
    SELECT 'bcn005' AS UserId, 'PL_P03' AS Frame, '1' AS Choice, 'Rules-Based'
    AS Description, '2011-10-24 14:14:26' AS [DateTime]
    UNION ALL
    SELECT 'bcn005' AS UserId, 'PL_P04' AS Frame, '0' AS Choice, 'VirtueBased'
    AS Description, '2011-10-24 14:16:37' AS [DateTime]
    UNION ALL
    SELECT 'bmk172' AS UserId, 'Prac_1' AS Frame, '0' AS Choice, 'None' AS
    Description, '2011-10-25 12:45:38' AS [DateTime]
) T

-- Dynamic SQL Preparation

DECLARE @SQL VARCHAR(MAX)
DECLARE @MaxNumberOfFrames INT

SET @SQL = ''
SET @MaxNumberOfFrames = 1

SELECT @MaxNumberOfFrames = MAX(FrameCount)
  FROM
    (
        SELECT COUNT(1) FrameCount
        FROM @FrameChoices
        GROUP BY UserId  
    ) T

 --needed to be part of the SQL string to EXEC at the bottom
SET @SQL = 'DECLARE @FrameChoices as TABLE(UserId varchar(50),Frame varchar(50),Choice varchar(4000),Description varchar(1000),[DateTime] DateTime)' 

SET @SQL = @SQL + ' INSERT INTO @FrameChoices(UserId,Frame,Choice,Description,[DateTime])'
SET @SQL = @SQL + ' SELECT  UserId,Frame,Choice,Description,[DateTime]'
SET @SQL = @SQL + ' FROM ('
SET @SQL = @SQL + '     SELECT ''bcn005'' AS UserId, ''PL_P03'' AS Frame, ''1'' AS Choice, ''Rules-Based'''
SET @SQL = @SQL + '     AS Description, ''2011-10-24 14:14:26'' AS [DateTime]'
SET @SQL = @SQL + '     UNION ALL'
SET @SQL = @SQL + '     SELECT ''bcn005'' AS UserId, ''PL_P04'' AS Frame, ''0'' AS Choice, ''VirtueBased'''
SET @SQL = @SQL + '     AS Description, ''2011-10-24 14:16:37'' AS [DateTime]'
SET @SQL = @SQL + '     UNION ALL'
SET @SQL = @SQL + '     SELECT ''bmk172'' AS UserId, ''Prac_1'' AS Frame, ''0'' AS Choice, ''None'' AS'
SET @SQL = @SQL + '     Description, ''2011-10-25 12:45:38'' AS [DateTime]'
SET @SQL = @SQL + ' ) T'

SET @SQL = @SQL + ' SELECT UserId'

--changed size to max
DECLARE @ColumnSQL VARCHAR(MAX)
DECLARE @CurrentFrame INT

SET @CurrentFrame = 1

WHILE (@CurrentFrame <= @MaxNumberOfFrames)
BEGIN
    SET @ColumnSQL = ', MAX(CASE WHEN RowNumber ='+CAST(@CurrentFrame AS VARCHAR)+ ' THEN Frame ELSE '''' END) AS Frame_'+CAST(@CurrentFrame AS VARCHAR)
                    + ', MAX(CASE WHEN RowNumber ='+CAST(@CurrentFrame AS VARCHAR)+ ' THEN Choice ELSE '''' END) AS Choice_'+CAST(@CurrentFrame AS VARCHAR)
                    + ', MAX(CASE WHEN RowNumber ='+CAST(@CurrentFrame AS VARCHAR)+ ' THEN Description ELSE '''' END) AS Description_'+CAST(@CurrentFrame AS VARCHAR)
                    + ', MAX(CASE WHEN RowNumber ='+CAST(@CurrentFrame AS VARCHAR)+ ' THEN [DateTime] ELSE NULL END) AS DateTime_'+CAST(@CurrentFrame AS VARCHAR)
    SET @SQL = @SQL + @ColumnSQL
    SET @CurrentFrame = @CurrentFrame + 1
END

SET @SQL = @SQL + ' FROM ( SELECT UserId,Frame,Choice,Description,[DateTime], ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY [DateTime]) AS RowNumber FROM @FrameChoices) T GROUP BY T.UserId'

PRINT @SQL

-- This generated SQL then can be executed by

EXEC(@SQL)
 -- OR
EXEC SP_EXECUTESQL(@SQL)

-- Sample SQL generated from above query

DECLARE @FrameChoices as TABLE(UserId varchar(50),Frame varchar(50),Choice varchar(4000),Description varchar(1000),[DateTime] DateTime) INSERT INTO @FrameChoices(UserId,Frame,Choice,Description,[DateTime]) SELECT  UserId,Frame,Choice,Description,[DateTime] FROM (     SELECT 'bcn005' AS UserId, 'PL_P03' AS Frame, '1' AS Choice, 'Rules-Based'  AS Description, '2011-10-24 14:14:26' AS [DateTime]     UNION ALL   SELECT 'bcn005' AS UserId, 'PL_P04' AS Frame, '0' AS Choice, 'VirtueBased'  AS Description, '2011-10-24 14:16:37' AS [DateTime]     UNION ALL   SELECT 'bmk172' AS UserId, 'Prac_1' AS Frame, '0' AS Choice, 'None' AS  Description, '2011-10-25 12:45:38' AS [DateTime] ) T SELECT UserId, MAX(CASE WHEN RowNumber =1 THEN Frame ELSE '' END) AS Frame_1, MAX(CASE WHEN RowNumber =1 THEN Choice ELSE '' END) AS Choice_1, MAX(CASE WHEN RowNumber =1 THEN Description ELSE '' END) AS Description_1, MAX(CASE WHEN RowNumber =1 THEN [DateTime] ELSE NULL END) AS DateTime_1, MAX(CASE WHEN RowNumber =2 THEN Frame ELSE '' END) AS Frame_2, MAX(CASE WHEN RowNumber =2 THEN Choice ELSE '' END) AS Choice_2, MAX(CASE WHEN RowNumber =2 THEN Description ELSE '' END) AS Description_2, MAX(CASE WHEN RowNumber =2 THEN [DateTime] ELSE NULL END) AS DateTime_2 FROM ( SELECT UserId,Frame,Choice,Description,[DateTime], ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY [DateTime]) AS RowNumber FROM @FrameChoices) T GROUP BY T.UserId

score 1 · Accepted Answer · edited May 23 '17 at 11:44

If SQL 2005 or ABove you can try pivot.

If SQL 2000, then use combination of Group By and Case.

SELECT GC1,
       GC2,
       MAX(CASE WHEN CONDITION1 THEN Value1 ELSE Value2 END) AS DerivedColumn
FROM Table1
GROUP BY GC1, GC2

References: SQL Server PIVOT and UNPIVOT

Pivot with Dynamic Column

Stack Overflow Pivot Question

[Solution]

-- Sample Data Preparation

DECLARE @FrameChoices TABLE
(
  UserId varchar(20),
  Frame varchar(20),
  Choice int,
  Description varchar(20),
  [DateTime] DateTime
)

INSERT INTO @FrameChoices(UserId,Frame,Choice,Description,[DateTime])
SELECT  UserId,Frame,Choice,Description,[DateTime]
  FROM (
        SELECT 'bcn005' AS UserId, 'PL_P03' AS Frame, 1 AS Choice, 'Rules-Based' AS Description, '2011-10-24 14:14:26' AS [DateTime]
        UNION ALL
        SELECT 'bcn005' AS UserId, 'PL_P04' AS Frame, 0 AS Choice, 'VirtueBased' AS Description, '2011-10-24 14:16:37' AS [DateTime]
        UNION ALL
        SELECT 'bmk172' AS UserId, 'Prac_1' AS Frame, 0 AS Choice, 'None' AS Description, '2011-10-25 12:45:38' AS [DateTime]
       ) T

-- Dynamic SQL Preparation

DECLARE @SQL VARCHAR(4000)
DECLARE @MaxNumberOfFrames INT

SET @SQL = ''
SET @MaxNumberOfFrames = 1

SELECT @MaxNumberOfFrames = MAX(FrameCount)
  FROM
(
SELECT COUNT(1)FrameCount
  FROM @FrameChoices
 GROUP BY UserId  
) T

SET @SQL = 'SELECT UserId'

DECLARE @ColumnSQL VARCHAR(512)
DECLARE @CurrentFrame INT

SET @CurrentFrame = 1

WHILE (@CurrentFrame <= @MaxNumberOfFrames)
BEGIN
    SET @ColumnSQL = ', MAX(CASE WHEN RowNumber ='+CAST(@CurrentFrame AS VARCHAR)+ ' THEN Frame ELSE NULL END) AS Frame_'+CAST(@CurrentFrame AS VARCHAR)
                    + ', MAX(CASE WHEN RowNumber ='+CAST(@CurrentFrame AS VARCHAR)+ ' THEN Choice ELSE NULL END) AS Choice_'+CAST(@CurrentFrame AS VARCHAR)
                    + ', MAX(CASE WHEN RowNumber ='+CAST(@CurrentFrame AS VARCHAR)+ ' THEN Description ELSE NULL END) AS Description_'+CAST(@CurrentFrame AS VARCHAR)
                    + ', MAX(CASE WHEN RowNumber ='+CAST(@CurrentFrame AS VARCHAR)+ ' THEN [DateTime] ELSE NULL END) AS DateTime_'+CAST(@CurrentFrame AS VARCHAR)
    SET @SQL = @SQL + @ColumnSQL
    SET @CurrentFrame = @CurrentFrame + 1
END

SET @SQL = @SQL + ' FROM ( SELECT UserId,Frame,Choice,Description,[DateTime], ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY [DateTime]) AS RowNumber FROM @FrameChoices) T GROUP BY T.UserId'

PRINT @SQL

-- This generated SQL then can be executed by

EXEC(@SQL)
 -- OR
EXEC SP_EXECUTESQL(@SQL)

-- Sample SQL generated from above query

SELECT UserId, MAX(CASE WHEN RowNumber =1 THEN Frame ELSE NULL END) AS Frame_1, MAX(CASE WHEN RowNumber =1 THEN Choice ELSE NULL END) AS Choice_1, MAX(CASE WHEN RowNumber =1 THEN Description ELSE NULL END) AS Description_1, MAX(CASE WHEN RowNumber =1 THEN [DateTime] ELSE NULL END) AS DateTime_1, MAX(CASE WHEN RowNumber =2 THEN Frame ELSE NULL END) AS Frame_2, MAX(CASE WHEN RowNumber =2 THEN Choice ELSE NULL END) AS Choice_2, MAX(CASE WHEN RowNumber =2 THEN Description ELSE NULL END) AS Description_2, MAX(CASE WHEN RowNumber =2 THEN [DateTime] ELSE NULL END) AS DateTime_2 FROM ( SELECT UserId,Frame,Choice,Description,[DateTime], ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY [DateTime]) AS RowNumber FROM @FrameChoices) T GROUP BY T.UserId

These examples would allow me to pivot the frame names into columns. I need to actually keep the columns I have in my query but add a new set of frame/choice/description/datetime for every row. — gooddadmike, Dec 05 '11 at 04:51
So it seems columns would be dynamic, only way to do it by a query is dynamic sql. Link in answer "Pivot with Dynamic Column" will solve your problem. — Nitin Midha, Dec 05 '11 at 15:01
Thanks. I know my answer is in there, but I am having a hard time figuring this out. — gooddadmike, Dec 05 '11 at 17:42
I have implemented the sample solution, check the updated answer. — Nitin Midha, Dec 05 '11 at 20:01
Thanks! It worked. To get results I had to declare as a table variable and include my query twice. Once to create the columns and once in the @sql string to build the data. — gooddadmike, Dec 07 '11 at 19:42

score 0 · Answer 2 · answered Dec 02 '11 at 22:41

0

What you want is the Pivot Table functionality of SQL Server

http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx

answered Dec 02 '11 at 22:41

David

72,686
18
132
173

See comment on Nitin Midha answer above. I am trying to make new columns for every row not change column values into columns. – gooddadmike Dec 05 '11 at 04:52

SQL Stored procedure, Output rows as columns for summary

2 Answers2