Selecting rows as columns and columns as rows.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting rows as columns and columns as rows.

Author

Topic

tariq_erp
Starting Member

9 Posts

Posted - 2008-02-09 : 02:46:07

Hi All

I have entries in a table like

C1 C2 C3
-- -- --
1 2 3
4 5 6
7 8 9

n with a single select statement I want to get this result.

C1 C2 C3
-- -- ---
1 4 7
2 5 8
3 6 9

plz suggest a solutions.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-09 : 09:13:57

Can the solution assume that your table will ALWAYS have 3 rows and 3 columns?
If not, what is the possible range of row counts in the source table?
How many columns are actually in the source table?
And, by the way, why do you want to do this?

Have you considered using Excel to do this. Highlight your grid | Copy | Paste Special (check "transpose" option) |

Be One with the Optimizer
TG

tariq_erp
Starting Member

9 Posts

Posted - 2008-02-11 : 04:21:00

Thanx for your kind consideration dear.

Infact it was an interview question.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 07:21:52

use PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-11 : 12:57:29

I do not use the PIVOT operator much.. ok well ever. But, I didn't think it could just flip a table in it's side like that because you had a to use an agregate function...? I'll play around with it a bit and see what I can come up with. If anyone can show how to solve that interview question with a pivot, I'd love to see a solution.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 21:05:41

[code]DECLARE @sample TABLE
(
C1 int,
C2 int,
C3 int
)
INSERT INTO @sample
SELECT 1, 2, 3 UNION ALL
SELECT 4, 5, 6 UNION ALL
SELECT 7, 8, 9

SELECT C1 = [1], C2 = [2], C3 = [3]
FROM
(
SELECT col = 1, C = C1,
row_no = row_number() OVER(ORDER BY C1)
FROM @sample
UNION ALL
SELECT col = 2, C = C2,
row_no = row_number() OVER(ORDER BY C2)
FROM @sample
UNION ALL
SELECT col = 3, C = C3,
row_no = row_number() OVER(ORDER BY C3)
FROM @sample
) d1
pivot
(
MAX(C)
FOR row_no IN ([1], [2], [3])
) p1

/*
C1 C2 C3
----------- ----------- -----------
1 4 7
2 5 8
3 6 9

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-12 : 12:14:54

Very nice Khtan! I was messing around and came up with this very simple method, but I definatly like the UNION/ROW_NUMBER version better!

DECLARE @T TABLE (C1 INT, C2 INT, C3 INT)

INSERT @T
SELECT 1, 2, 3
UNION ALL SELECT 4, 5, 6
UNION ALL SELECT 7, 8, 9



SELECT [2] AS C1, [5] AS C2,  AS C3
FROM
(
	SELECT c1, c2
	FROM @T AS T
) AS p
PIVOT
(
	MIN(c1)
	FOR C2 IN ([2], [5], )
) AS pvt

UNION

SELECT [1] AS C1, [4] AS C2, [7] AS C3
FROM
(
	SELECT c1, c2
	FROM @T AS T
) AS p
PIVOT
(
	MIN(c2)
	FOR C1 IN ([1], [4], [7])
) AS pvt

UNION

SELECT [2] AS C1, [5] AS C2,  AS C3
FROM
(
	SELECT c2, c3
	FROM @T AS T
) AS p
PIVOT
(
	MIN(c3)
	FOR C2 IN ([2], [5], )
) AS pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources