Converting rows to columns - SQL Server Forums

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)

Converting rows to columns

Author

Topic

mtoru
Starting Member

8 Posts

Posted - 2010-06-03 : 07:38:57

I need to convert rows to column.

The Original Tables :

First Table :
ID Name Last Name
1 David Climb
2 George Josh
3 Steve Act

Second table :

CustomerID Brand Model
1 HP ABX
1 Canon TD-1
1 IBM XOS
2 HP ABT
2 Xerox 122
3 Xerox 122

The table I need to get :

ID Name Last Name Brand1 Model1 Brand2 Model2 Brand3 Model3
1 David Climb HP ABX Canon TD-1 IBM XOS
2 George Josh HP ABT XEROX 122
3 Steve Act Xerox 122

How can I do that?

Thanks;

Sachin.Nand

2937 Posts

Posted - 2010-06-03 : 08:05:53

Are those brands static?

PBUH

mtoru
Starting Member

8 Posts

Posted - 2010-06-03 : 08:09:29

What do you mean by "static"?

If It will be useful info for you; every customer can have three brands max.
That means there will be only 3 brand columns in the final table.

Sachin.Nand

2937 Posts

Posted - 2010-06-03 : 08:43:02

[code]
select Name,Last Name
max(case when Brand='HP' Then Brand end) as Brand1,
max(case when Model='ABX' Then Model end) as Model1
You have to do it for all the Brand and columns in the table
from table2 inner join to table1 on table1.id=table2.id group by Name,Last Name
[/code]

PBUH

mtoru
Starting Member

8 Posts

Posted - 2010-06-03 : 10:27:49

There are a lot of brands in the table. I want to make it dynamic. I mean Find the all brands and model and put them into the columns. I dont want to do it by CASE sentence.

find the Customer ID, Get the first brand and model, put them into the Brand1 and model1
IF EXISTS(find the Customer ID, Get the second brand and model, put them into the Brand2 and model2)
IF EXISTS(find the Customer ID, Get the third brand and model, put them into the Brand3 and model3)

This type of solution will be appricated.

By the way; thank you so much for the suggestions Idera.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 11:09:09

Search for "Dynamic Cross Tab" in the Search widget at the top of the page.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-03 : 11:25:36

[code]DECLARE @Table1 table(Id int,fName varchar(20),lName varchar(20))
DECLARE @Table2 table(CustomerId int,Brand varchar(20),Model varchar(20))

INSERT INTO @table1

SELECT 1,'David','Climb' UNION
SELECT 2,'George','Josh' UNION
SELECT 3,'Steve','Act'

insert into @table2
SELECT 1,'HP','ABX' UNION
SELECT 1,'Canon','TD-1' UNION
SELECT 1,'IBM','XOS' UNION
SELECT 2,'HP','ABT' UNION
SELECT 2,'Xerox','122' UNION
SELECT 3,'Xerox','122'

SELECT fName, lName
,[Brand1] = max(CASE WHEN rank = 1 then brand end)
,[Model1] = max(CASE WHEN rank = 1 then Model end)
,[Brand2] = max(CASE WHEN rank = 2 then brand end)
,[Model2] = max(CASE WHEN rank = 2 then Model end)
,[Brand3] = max(CASE WHEN rank = 3 then brand end)
,[Model3] = max(CASE WHEN rank = 3 then Model end)
FROM
(
select t1.id,t1.fname,t1.lname,t2.brand,t2.model
,[Rank] = rank() OVER(partition by t1.fname,t1.lname order by brand,model)
from
@table1 t1
inner join @table2 t2 on
t1.id = t2.customerid

) a

group by fName, lName[/code]

jim

Everyday I learn something that somebody else already knew

mtoru
Starting Member

8 Posts

Posted - 2010-06-03 : 12:23:39

In this case, I should use a variable for RANK in a while..end loop. Right? There are 120.000 unique Customer and 267.000 Brand Model variation.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-03 : 13:34:23

You shouldn't need a while loop for anything or a variable for rank. As long as each customer is limited to 3, you should be okay. You can also add where [rank] < 4 before group by fname,lname to force a limit of three.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources