Transpose in SQL
-
I have a table that has quite a few columns. I'd like to transpose it into a column format. Here is an example of before and after.
BEFORE
AFTER (column 1 becomes field names, column 2 is row 1 of data, etc)
I looked around for code but everything I found was pretty confusing. Thanks.
I use SQL 2000
-
We can't see your pictures since they are stored on your hard drive C:
Upload you pictures to a public web site (where we have access to read) and change the url in your post.
N 56°04'39.16"
E 12°55'05.25" -
I get a "403 Permission Denied" error....
N 56°04'39.16"
E 12°55'05.25" -
I'm having problems posting a jpg, go figure. I'll just type it in.
There are lots more columns, I'll just do a few. Top row is field name.
BEFORE:
Name Address City Zip Joe 111 Main Dallas 76211 Ed 222 Bend Dallas 75207 AFTER
Column1 Column2 Column3
Name Joe Ed Address 111 Main 222 Bend City Dallas Dallas Zip 76211 75207 -
Effecitevely you are looking for a Pivot table, good article here:
http://qa.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp
-
You might want to have a look at this, too. It's about the same issue, rather than a crosstab (aggregate) query like the previous link.
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=291923
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Something I threw together in a rush for another query (had a few mins to kill). If you're on 2000 and run low on options you could always modify it to suit your needs.
Best to limit to small scale for this sort of thing. If you're using SQL 2k though and don't have PIVOT features available, I've drafted a stored proc that should do the job for you. Bit of a botch rush job so pull it apart as much as you like. Paste the below into a sql window and edit the EXEC at the bottom as preferred. If you want to see what's being generated, remove the --s in the middle:
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P' AND NAME = 'USP_LIST_CONCAT')
DROP PROCEDURE USP_LIST_CONCAT
GO
CREATE PROCEDURE USP_LIST_CONCAT (@SourceTable NVARCHAR(1000) = '' ,@SplitColumn NVARCHAR(1000) = '' , @Deli NVARCHAR(10) = '', @KeyColumns NVARCHAR(2000) = '' , @Condition NVARCHAR(1000) = '')
AS
BEGIN
SET NOCOUNT ON
/* PROCEDURE CREATED 2010 FOR SQL SERVER 2000. SIMON HUGHES. */
/* NOTES: REMOVE --'s BELOW TO LIST GENERATED SQL. */
IF @SourceTable = '' OR @SourceTable = '?' OR @SourceTable = '/?' OR @SplitColumn = '' OR @KeyColumns = ''
BEGIN
PRINT 'Format for use:'
PRINT ' USP_LIST_CONCAT ''SourceTable'', ''SplitColumn'', ''Deli'', ''KeyColumn1,...'', ''Column1 = 12345 AND ...'''
PRINT ''
PRINT 'Description:'
PRINT 'The SourceTable should contain a number of records acting as a list of values.'
PRINT 'The SplitColumn should be the name of the column holding the values wanted.'
PRINT 'The Delimiter may be any single character or string ie ''/'''
PRINT 'The KeyColumn may contain a comma seperated list of columns that will be returned before the concatenated list.'
PRINT 'The optional Conditions may be left blank or may include the following as examples:'
PRINT ' ''Column1 = 12334 AND (Column2 = ''ABC'' OR Column3 = ''DEF'')'''
PRINT ''
PRINT 'A standard list in the format:'
PRINT ' Store1, Employee1, Rabbits'
PRINT ' Store1, Employee1, Dogs'
PRINT ' Store1, Employee1, Cats'
PRINT ' Store1, Employee2, Dogs'
PRINT ''
PRINT 'Will be returned as:'
PRINT ' Store1, Employee1, Cats/Dogs/Rabbits'
PRINT ' Store1, Employee2, Dogs'
PRINT ''
PRINT 'A full ORDER BY and DISTINCT is included'
RETURN -1
END
DECLARE @SQLStatement NVARCHAR(4000)
SELECT @SQLStatement = '
DECLARE @DynamicSQLStatement NVARCHAR(4000)
SELECT @DynamicSQLStatement = ''SELECT '+@KeyColumns+', SUBSTRING(''
SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' + '' + CHAR(10) +
'' MAX(CASE WHEN '+@SplitColumn+' = ''''''+RTRIM('+@SplitColumn+')+'''''' THEN '''''+@Deli+'''+RTRIM('+@SplitColumn+')+'''''' ELSE '''''''' END)''
FROM '+ @SourceTable +' ORDER BY '+@SplitColumn+'
SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' ,2,7999) List'' + CHAR(10) + ''FROM '+ @SourceTable+''' + CHAR(10) +'''+CASE WHEN @Condition = '' THEN '/* WHERE */' ELSE 'WHERE '+@Condition END+ '''+ CHAR(10) + ''GROUP BY '+@KeyColumns+'''
SELECT @DynamicSQLStatement = REPLACE(@DynamicSQLStatement,''( +'',''('')
-- SELECT @DynamicSQLStatement -- DEBUG ONLY
EXEC (@DynamicSQLStatement)'
EXEC (@SQLStatement)
END
GO
EXEC USP_LIST_CONCAT 'MyTableName', 'ColumnForListing', 'Delimiter', 'KeyCol1, KeyCol2', 'Column1 = 123456'
-
In terms of the data you present here, the following will transpose your table to the desired format. You may see a pattern here which you could build up dynamically for more extensive datasets.
IF NOT OBJECT_ID('tempdb.dbo.#Before', 'U') IS NULLDROP TABLE #Before
CREATE TABLE #Before
(
Name VARCHAR(50),
Address VARCHAR(50),
City VARCHAR(50),
Zip VARCHAR(50)
)
INSERT #Before
SELECT
'Joe' AS Name, '111 Main' AS Address, 'Dallas' AS City, '76211' AS Zip
UNION ALL SELECT 'Ed', '222 Bend', 'Dallas', '75207' --Recreates your Before table
ALTER TABLE #Before ADD IDINT INT IDENTITY (2, 1) --Gives a column pointer to transpose data
SELECT * FROM #Before
SELECT 'Name' AS Column1, (SELECT Name FROM #Before WHERE IDINT = 2) AS Column2, (SELECT Name FROM #Before WHERE IDINT = 3) AS Column3
UNION ALL SELECT 'Address', (SELECT Address FROM #Before WHERE IDINT = 2), (SELECT Address FROM #Before WHERE IDINT = 3)
UNION ALL SELECT 'City', (SELECT City FROM #Before WHERE IDINT = 2), (SELECT City FROM #Before WHERE IDINT = 3)
UNION ALL SELECT 'Zip', (SELECT Zip FROM #Before WHERE IDINT = 2), (SELECT Zip FROM #Before WHERE IDINT = 3)
-
In SQL Server 2005 etc you can avoid all the subqueries by using UNPIVOT and PIVOT as well as tapping in to the ROW_NUMBER function
IF NOT OBJECT_ID('tempdb.dbo.#Before', 'U') IS NULLDROP TABLE #Before
CREATE TABLE #Before
(
Name VARCHAR(50),
Address VARCHAR(50),
City VARCHAR(50),
Zip VARCHAR(50)
)
INSERT #Before
SELECT
'Joe' AS Name, '111 Main' AS Address, 'Dallas' AS City, '76211' AS Zip
UNION ALL SELECT 'Ed', '222 Bend', 'Dallas', '75207'
SELECT * FROM #Before
;
WITH cteUnpivot
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY IDINT ORDER BY (SELECT 0)) AS ROW, IDINT,
NULLIF(Details, '') AS Details, Headers FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 1 AS IDINT,
COALESCE(Name, '') AS Name , COALESCE(Address, '') AS Address, COALESCE(City, '') AS City, COALESCE(Zip, '') AS Zip
FROM #Before
) AS Z
UNPIVOT (Details FOR Headers IN (Name, Address, City, Zip)) AS Z
)
SELECT Headers AS Column1, [2] AS Column2, [3] AS Column3 FROM cteUnpivot
PIVOT (MAX(Details) FOR IDINT IN ([2], [3])) AS Z
ORDER BY ROW
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply