December 4, 2015 at 12:59 pm
I have a simple example of trying to transpose columns to rows.
Before:
column xcolumn ycolumn z
Alpha Delta Bravo
After:
column xAlpha
column yDelta
column zBravo
The data is this simple, and the values in each column are text, with no numeric values associated to any of the columns. Also there is no prediction for the number of columns that there can be.
December 4, 2015 at 1:11 pm
A simple Unpivot works but if you don't know how many columns there can be you will need to look at a Dynamic Query.
DECLARE @test-2 TABLE (colx VARCHAR(8), coly VARCHAR(8), colz VARCHAR(8))
INSERT INTO @test-2
VALUES ('Alpha', 'Delta', 'Bravo')
SELECT
SomeValue
FROM (
SELECT * FROM @test-2
) AS x
UNPIVOT
(
SomeValue FOR SomeValues IN (colx, coly, colz)
) up
December 4, 2015 at 1:37 pm
I prefer to do it using CROSS APPLY and table constructors. Here's an article on that: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (colx VARCHAR(8), coly VARCHAR(8), colz VARCHAR(8))
INSERT INTO #test
VALUES ('Alpha', 'Delta', 'Bravo')
--Common unpivot
SELECT ColName, SomeValue
FROM (SELECT * FROM #test) AS x
UNPIVOT (SomeValue FOR ColName IN (colx, coly, colz)) up;
--Alternative static
SELECT ColName, SomeValue
FROM #test
CROSS APPLY( VALUES('colx', colx),
('coly', coly),
('colz', colz)) u(ColName, SomeValue);
--Alternative Dynamic
DECLARE @sql nvarchar(max);
SET @sql = N'SELECT ColName, SomeValue
FROM #test
CROSS APPLY( VALUES' + STUFF((SELECT CHAR(10) + REPLICATE( CHAR(9), 4) + ',(' + QUOTENAME( name, '''') + ', ' + QUOTENAME( name, '''') + ')'
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#test')
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 6, '') + ') u(ColName, SomeValue);';
EXEC sp_executesql @sql;
December 4, 2015 at 1:44 pm
Had some time so I added my Dynamic solution. Note that I forgot to add the column names in my first example but it looks like Luis had you covered.
CREATE TABLE #test (colx VARCHAR(8), coly VARCHAR(8), colz VARCHAR(8))
INSERT INTO #test
VALUES ('Alpha', 'Delta', 'Bravo');
WITH getColumns (myColumn) AS
(
SELECT name FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#test')
)
SELECT @ColumnList = ISNULL(@ColumnList,'') + myColumn + ', ' FROM getColumns
SET @ColumnList = SUBSTRING(@ColumnList, 1, LEN(@ColumnList)-1)
SET @stmt =
'SELECT
ColumnName, SomeValue
FROM (
SELECT * FROM #test
) AS x
UNPIVOT
(
SomeValue FOR ColumnName IN (' + @ColumnList + ')
) up
'
EXECUTE (@stmt)
DROP TABLE #test
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply