Pls help. Transposing 1 column to rows in data

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 2008 Forums

Transact-SQL (2008)

Pls help. Transposing 1 column to rows in data

Author

Topic

sql_query111
Starting Member

4 Posts

Posted - 2012-01-09 : 11:37:32

Hi Guys,

I do not know SQL server and am using SQL. Kindly help me with the following. I have data in the following format

Category Value
x 1.1
x 2.3
x 4.3
p 1.5
p 7.6

The dataset is quite large with many other columns and rows.
I want the output in the following format

Category Value1 Value2 Value3
x 1.1 2.3 4.3
p 1.5 7.6

Pls tell me how to do this in SQl.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 11:42:06

[code]
SELECT Category,
MAX(CASE WHEN Rn=1 THEN Value END) AS Value1,
MAX(CASE WHEN Rn=2 THEN Value END) AS Value2,
MAX(CASE WHEN Rn=3 THEN Value END) AS Value3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Value) AS Rn,*
FROM table
)t
GROUP BY Category
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-09 : 11:53:56

quote:
Originally posted by visakh16

SELECT Category,
MAX(CASE WHEN Rn=1 THEN Value END) AS Value1,
MAX(CASE WHEN Rn=2 THEN Value END) AS Value2,
MAX(CASE WHEN Rn=3 THEN Value END) AS Value3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Value) AS Rn,*
FROM table
)t
GROUP BY Category

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If I am reading this correctly, this will get you a static pivot table, without having to use the pivot syntax. Is that correct? If so, that is very clever.

FischMan

sql_query111
Starting Member

4 Posts

Posted - 2012-01-09 : 12:01:29

Thanks for ur replies.. can u pls explain what is Rn variable and how do I get it. Also is ROW_NUMBER a keyword>?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 12:10:58

quote:
Originally posted by sql_query111

Thanks for ur replies.. can u pls explain what is Rn variable and how do I get it. Also is ROW_NUMBER a keyword>?

Rn is just a sequence variable to number values coming inside each category group.
ROW_NUMBER() is function in sql server

http://msdn.microsoft.com/en-us/library/ms186734.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 12:11:52

quote:
Originally posted by FischMan2

quote:
Originally posted by visakh16

SELECT Category,
MAX(CASE WHEN Rn=1 THEN Value END) AS Value1,
MAX(CASE WHEN Rn=2 THEN Value END) AS Value2,
MAX(CASE WHEN Rn=3 THEN Value END) AS Value3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Value) AS Rn,*
FROM table
)t
GROUP BY Category

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If I am reading this correctly, this will get you a static pivot table, without having to use the pivot syntax. Is that correct? If so, that is very clever.

FischMan

Yep..You're correct
This is how you do it without using pivot operator particular in versions before 2005 where PIVOT is not available

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql_query111
Starting Member

4 Posts

Posted - 2012-01-09 : 12:39:13

I am using SQL , is ROW_NUMBER a function in SQl too?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 04:10:10

quote:
Originally posted by sql_query111

I am using SQL , is ROW_NUMBER a function in SQl too?

SQL is generic
tell us which implemetation you're using
SQL Server T-SQL, Oracle PL/SQL or something else?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql_query111
Starting Member

4 Posts

Posted - 2012-01-10 : 06:05:44

SQL in SAS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 06:53:49

I'm not sure of that as I dunno SAS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources