row to column

Question

Post reply

row to column

dpalani

SSC Journeyman

Points: 83
More actions
January 11, 2011 at 3:13 am

#233118

Hi,
I want to write a query to give row to column group by account.
Below are the eg. Can any one help me out.
create table test
( account varchar(30),
dataset varchar(30),
stringvalue varchar (30))
insert into test values ( '1', 'col1', 'ONE')
insert into test values ( '1', 'col2', 'TWO')
insert into test values ( '1', 'col3', 'THREE')
insert into test values ( '2', 'col1', 'ONE')
insert into test values ( '2', 'col2', 'TWO')
insert into test values ( '2', 'col3', 'THREE')
insert into test values ( '3', 'col1', 'xxx')
insert into test values ( '3', 'col3', 'zzzzz')
expected result set
account col1col2col3
1 ONETWOTHREE
2 ONETWOTHREE
3 xxxnullzzzzz

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

roi.reuven SSCommitted Points: 1700 More actions · Answer 1

this

select col3,col2,col1,coalesce(z.account,y.account,x.account) as account from

(select stringvalue as 'col3',account

from test where dataset='col3'

)as z

full join

(select stringvalue as 'col2',account

from test where dataset='col2'

) as y

on z.account=y.account

full join

(select stringvalue as 'col1',account

from test where dataset='col1'

)as x

on x.account=z.account

LutzM SSC Guru Points: 107049 More actions · Answer 2

Another method would be a CrossTab query.

For details please see the related link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

dpalani SSC Journeyman Points: 83 More actions · Answer 3

Thanks

in the example i have said three columns, But i have more than 20 rows to be tranfered to column, And the data is huge. it will hit the perfromance. Do we have any other logic to do the same task.

roi.reuven SSCommitted Points: 1700 More actions · Answer 4

Maybe if you tell me what you are trying to solve, why you need the query I could help,

Just might be a better solution for the problem you are trying to deal with

roi.reuven SSCommitted Points: 1700 More actions · Answer 5

Here is an example of something that is doing the

Reverse of what you are trying to do

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

Emp3 int, Emp4 int, Emp5 int);

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4);

INSERT INTO pvt VALUES (2,4,1,5,5,5);

INSERT INTO pvt VALUES (3,4,3,5,4,4);

INSERT INTO pvt VALUES (4,4,2,5,5,4);

INSERT INTO pvt VALUES (5,5,1,5,5,5);

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM

(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

FROM pvt) p

UNPIVOT

(Orders FOR Employee IN

(Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt;

LutzM SSC Guru Points: 107049 More actions · Answer 6

dpalani (1/11/2011)
Thanks
in the example i have said three columns, But i have more than 20 rows to be tranfered to column, And the data is huge. it will hit the perfromance. Do we have any other logic to do the same task.

Did you read the CrossTab article I pointed you at?

It's most probably one of the best performing methods except for doing stuff like that at the presentation layer (e.g. Reporting Services), which would be the most recommended solution 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

roi.reuven SSCommitted Points: 1700 More actions · Answer 7

this

Select * From

( Select account, dataset, stringvalue

From test) as A

Pivot

(

min(stringvalue)

for

dataset in (col1, col2, col3)

) as P