June 21, 2012 at 4:12 pm
Hey guys,
I am a little stumped. I am trying to pull data from one column(could have anywhere from 1-20 distinct values) and transpose that into columns.
For example - I have a table called 'items' with a column named 'codes' - field is a varchar(3)
If I run the following query - select distinct codes from items - I get the following:
Codes
------
1D2
5D3
6H2
4D2
What I would like to do is transpose this so that I get something similiar to this:
Code1 code2 code3 code4
1D2 5D3 6H2 4D2
I have looked at the pivot - but the examples I saw how multiple columns. I just need to transpose the 1. Any ideas?
June 21, 2012 at 5:31 pm
If 20 is a hard number then something like this will get you what you are after.
CREATE TABLE #cds (codes VARCHAR(10))
INSERT INTO #cds
( codes )
VALUES ( '1D2'), ('5D3'), ('6H2'), ('4D2')
SELECT Code01, Code02, Code03, Code04, Code05, Code06, Code07, Code08, Code09, Code10,
Code11, Code12, Code13, Code14, Code15, Code16, Code17, Code18, Code19, Code20
FROM (
SELECT TOP 20 'Code'+RIGHT('0'+CAST(ROW_NUMBER() OVER (ORDER BY codes) AS VARCHAR(10)), 4) num, codes
FROM #cds ORDER BY codes
) AS pvt
PIVOT (MAX(codes) FOR num IN (Code01, Code02, Code03, Code04, Code05, Code06, Code07, Code08, Code09, Code10,
Code11, Code12, Code13, Code14, Code15, Code16, Code17, Code18, Code19, Code20)) pt
June 21, 2012 at 5:45 pm
That is an awesome and impressive query. Thanks for the help!
June 22, 2012 at 7:39 am
Take a look at the links in my signature about cross tabs. The first one covers PIVOT and some alternative ways to do the same thing. The second one talks about how to handle this type of thing when you don't know how many columns you will end up with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply