Row To Column

Question

Post reply

Row To Column

joemai

Hall of Fame

Points: 3496
More actions
April 24, 2006 at 3:49 pm

#190282

Please someone show me how to convert data from row to column:
ex. Product      Product_Choice
671703420
  158779036X
671703420    1587790637
671703420     SM96
671703420     1889241075
I'd like to have something like this
product     product_choice1   product_choice2 product_choice3 product_choice4
671703420 158779036X 1587790637 SM96 1889241075
Thanks
Minh Vu

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

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

fpoon SSC Enthusiast Points: 171 More actions · Answer 1

I am not sure I have a clear picture on your data model, but here is my first stab.

CREATE TABLE Product

(

Product varchar(100),

Product_Choice varchar(100)

)

INSERT Product values ('671703420','158779036X')

INSERT Product values ('671703420','1587790637')

INSERT Product values ('671703420','SM96')

INSERT Product values ('671703420','1889241075')

SELECT

p.Product

, a.product_choice AS prod1

, b.product_choice AS prod2

, c.product_choice AS prod3

, d.product_choice AS prod4

FROM

(SELECT DISTINCT Product FROM Product) p inner join

(SELECT Product, product_choice FROM Product WHERE Product_CHOICE = '158779036X') a ON p.product = a.product inner join

(SELECT Product, product_choice FROM Product WHERE Product_CHOICE = '1587790637') b ON p.product = b.product inner join

(SELECT Product, product_choice FROM Product WHERE Product_CHOICE = 'SM96') c ON p.product = c.product inner join

(SELECT Product, product_choice FROM Product WHERE Product_CHOICE = '1889241075') d ON p.product = d.product

joemai Hall of Fame Points: 3496 More actions · Answer 2

Thanks for the input. However, the table is huge, I can not pass the product_choice one by one like your.

I think of some kind of cursor needed, but I don't know how yet.

Minh Vu

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Minh,

Do you actually need separate columns or can the data look like this...

Product ProductChoices

---------------- -------------------------------------------------------------------

671703420 '158779036X 1587790637 SM96 1889241075'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andreas AJV SSC Eights! Points: 800 More actions · Answer 4

What do you mean by "the table is huge" ?

Is there a very big number of rows?

If this is the case then... it is very possible that you end up with an analogous big number of Columns... which is (for my opinion) from difficult to impossible to read.

------------
When you 've got a hammer, everything starts to look like a nail...

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Hi Minh Vu (and all),

It looks like your example isn't a sufficient representation of your problem. Can you 'enhance' it for us please?

Here's one trick that might be useful (it's hard to know without more details)...

--data

declare @t table (Product int, Product_Choice varchar(30))

insert @t

select 671703420, '158779036X'

union all select 671703420, '1587790637'

union all select 671703420, 'SM96'

union all select 671703420, '1889241075'

--input

declare @product int

set @product = 671703420

declare @s-2 varchar(8000)

set @s-2 = 'select ' + cast(@Product as varchar(30)) + ' as Product, '

select @s-2 = + @s-2 + '''' + Product_Choice + ''', ' from @t where Product = @product

set @s-2 = left(@s, len(@s)-1)

exec (@s)

And this link can be very useful when it comes to doing pivots...

http://www.sqlteam.com/item.asp?ItemID=2955

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

John-79567 SSCommitted Points: 1892 More actions · Answer 6

I was wondering. How would you take a solution similar to yours but make the column headers equal the value of a column of record. For example:

declare @t table (Product int, Product_Choice varchar(30))

insert @t

select 671703420, 'Department', '158779036X'

union all select 671703420, 'Document', '1587790637'

union all select 671703420, 'EmployeeID, 'SM96'

union all select 671703420, 'Seat', '1889241075'

Result should look like:

Product Department Document EmployeeeID Seat

671703420 158779036X 1587790637 SM96 1889241075

joemai Hall of Fame Points: 3496 More actions · Answer 7

Sorry for not being clear enough.

Yes the table is huge meant there is about 2 millions records. However, it product will have 5 product choices is the most. And I do want each product_choice will stand on its own column.

I really wish I can make this:

product product_choice1 product_choice2 product_choice3 product_choice4

671703420 158779036X 1587790637 SM96 1889241075

Thanks for all of the inputs.

Minh Vu

RyanRandall SSChampion Points: 13623 More actions · Answer 8

Try this...

--data

if object_id('tempdb.dbo.#t') is not null drop table #t

create table #t (Product int, x varchar(20), Product_Choice varchar(12))

insert #t

select 671703420, 'Department', '158779036X'

union all select 671703420, 'Document', '1587790637'

union all select 671703420, 'EmployeeID', 'SM96'

union all select 671703420, 'Seat', '1889241075'

--calculation

exec dbo.crosstab 'select Product from #t group by Product', 'max(Product_Choice)','x','#t'

Output:

Product Department Document EmployeeID Seat

----------- ------------ ------------ ------------ ------------

671703420 158779036X 1587790637 SM96 1889241075

See http://www.sqlteam.com/item.asp?ItemID=2955 for the crosstab sp.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

671703420	158779036X
671703420	1587790637
671703420	SM96
671703420	1889241075