Transform Columns to Rows dynamically using T-SQL
-
Hi All,
I have the following table :
tbl_Survey
My goal is to transform it into the following format (to transpose Columns into Rows):
To achieve this I am using the following code (at the end of this post).
It works perfectly, but only for these values [Quest_1] [Quest_2] [Quest_3]
My reality - that a new values like "Quest_4" or "Question_5" or - anything text could be potentially added to an existing values in my table.
Is there any way to code - dynamically - so that no matter how many more values added - I'd still have the new transposed (unpivoted) table with these new values?
Not necessary could be UNPIVOT function.
Might be anything else... Any method (as long as it's in T-SQL)
Thank you
SELECT [SurveyID], [Question], [Rating]
FROM [dbo].[tbl_Survey]
UNPIVOT
(
[Rating]
FOR [Question] in ([Quest_1], [Quest_2], [Quest_3])
) AS SurveyUnpivot -
I have adapted the code from the following 2 links in order to create a sample of how to dynamically unpivot data
- http://stackoverflow.com/questions/18775409/unpivot-with-dynamic-columns-plus-column-names
- https://www.sqlservercentral.com/articles/an-alternative-better-method-to-unpivot-sql-spackle
if object_id('tempdb..#temp') is not null
drop table #temp;
GO
create table #temp
(
date datetime,
ABC money,
DEF money,
GHI VARCHAR(50)
);
insert into #temp(date, ABC, DEF, GHI) values ('1/1/2012', 1000.00, NULL, NULL);
insert into #temp(date, ABC, DEF, GHI) values ('2/1/2012', NULL, 500.00, 'Hello');
insert into #temp(date, ABC, DEF, GHI) values ('2/10/2012', NULL, 700.00, NULL);
insert into #temp(date, ABC, DEF, GHI) values ('3/1/2012', 1100.00, NULL, 'World');
--=======================================================================
DECLARE
@stcCols NVARCHAR(MAX)
, @pvtCols NVARCHAR(MAX)
, @cstCols NVARCHAR(MAX)
, @Query NVARCHAR(MAX)
, @ForceDataType NVARCHAR(50) = 'nvarchar(max)' --NULL --
, @Debug BIT = 1; -- Set to 1 to output the final query being used to unpivot the data.
DECLARE @tbCols TABLE (
colID INT NOT NULL
, colName SYSNAME NOT NULL
, isStatic BIT NOT NULL DEFAULT(0)
, hasError BIT NOT NULL DEFAULT(0)
);
INSERT INTO @tbCols (colID, colName)
SELECT column_id, name
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#temp')
ORDER BY column_id;
-- Idenify the columns we want to keep static
UPDATE @tbCols
SET isStatic = 1
WHERE colName IN ('date', '');
-- Build list of columns we want to remain static
SET @stcCols = STUFF( (SELECT DISTINCT ',' + QUOTENAME(c.colName)
FROM @tbCols c
WHERE isStatic = 1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
-- Build list of cols we want to unpivot with the required datatype
SET @cstCols = STUFF( (SELECT ', (''' + c.colName + ''', ' + CASE WHEN @ForceDataType IS NULL THEN QUOTENAME(c.colName)
ELSE 'CAST(' + QUOTENAME(c.colName) + ' AS ' + @ForceDataType + '))'
END + CHAR(13)+CHAR(10)
FROM @tbCols c
WHERE isStatic = 0
GROUP BY c.colName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, ' ');
SET @Query = N'
SELECT ' + @stcCols + ', colName, Val
FROM #temp
CROSS APPLY (VALUES ' + CHAR(13)+CHAR(10) + @cstCols + ') x (colName, Val)
WHERE Val IS NOT NULL;
'
IF (@Debug = 1) PRINT (@Query);
EXEC sp_executesql @Query;
drop table #temp;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply