Main Page | Report this Page
Computers Forum Index  »  Computer - Databases - MS SQL Server  »  Convert rows data to column data...
Page 1 of 1    

Convert rows data to column data...

Author Message
Lauren Quantrell...
Posted: Wed Oct 07, 2009 12:07 am
Guest
I have the following data returned by this query:
SELECT TOP 20
t.ID,
t.Name
FROM
tblContacts t
ORDER BY
t.Name

ID NAME
1 Betty
3 Jane
4 Mary
2 Susan
....
20 Zelda

What I want to do is to insert the results above into a temp table so
that this query:

SELECT
Name1,
Name2,
Name3,
Name4,
....
Name20
FROM #TempTable

returns the following recordset:

Name1 = Betty
Name2 = Jane
Name3 = Mary
Name4 = Susan
,,,
Name20 = Zelda

There will always be 20 rows created by the first query so the temp
could have 20 static column names.
I'm at a loss to figure out how to construct this.

Thanks for any guidance,
lq
 
Plamen Ratchev...
Posted: Wed Oct 07, 2009 5:11 am
Guest
If the id column is sequential as you listed in your example, then it is easy to pivot based on the id:

SELECT MAX(CASE WHEN ID = 1 THEN Name END) AS name1,
MAX(CASE WHEN ID = 2 THEN Name END) AS name2,
...
MAX(CASE WHEN ID = 20 THEN Name END) AS name20
FROM (
SELECT TOP 20 t.ID, t.Name
FROM tblContacts AS t
ORDER BY t.Name) AS T;

If the id column is not sequential then you can use the ranking functions (SQL Server 2005/2008) and pivot on the rank:

SELECT MAX(CASE WHEN rk = 1 THEN Name END) AS name1,
MAX(CASE WHEN rk = 2 THEN Name END) AS name2,
...
MAX(CASE WHEN rk = 20 THEN Name END) AS name20
FROM (
SELECT TOP 20 t.ID, t.Name, ROW_NUMBER() OVER(ORDER BY t.Name) AS rk
FROM tblContacts AS t
ORDER BY t.Name) AS T;

--
Plamen Ratchev
http://www.SQLStudio.com
 
Lauren Quantrell...
Posted: Wed Oct 07, 2009 1:07 pm
Guest
Yes, that does the trick. I should have thought of that!
Much thanks.
lq


On Oct 6, 9:11 pm, Plamen Ratchev <Pla... at (no spam) SQLStudio.com> wrote:
Quote:
If the id column is sequential as you listed in your example, then it is easy to pivot based on the id:

SELECT MAX(CASE WHEN ID = 1 THEN Name END) AS name1,
        MAX(CASE WHEN ID = 2 THEN Name END) AS name2,
        ...
        MAX(CASE WHEN ID = 20 THEN Name END) AS name20
FROM (
SELECT TOP 20 t.ID, t.Name
FROM tblContacts AS t
ORDER BY t.Name) AS T;

If the id column is not sequential then you can use the ranking functions (SQL Server 2005/2008) and pivot on the rank:

SELECT MAX(CASE WHEN rk = 1 THEN Name END) AS name1,
        MAX(CASE WHEN rk = 2 THEN Name END) AS name2,
        ...
        MAX(CASE WHEN rk = 20 THEN Name END) AS name20
FROM (
SELECT TOP 20 t.ID, t.Name, ROW_NUMBER() OVER(ORDER BY t.Name) AS rk
FROM tblContacts AS t
ORDER BY t.Name) AS T;

--
Plamen Ratchevhttp://www.SQLStudio.com
 
 
Page 1 of 1    
All times are GMT
The time now is Fri Dec 11, 2009 2:27 am