Simple pivot / join query - 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 2000 Forums

Transact-SQL (2000)

Simple pivot / join query

Author

Topic

sfletcher
Starting Member

6 Posts

Posted - 2009-01-29 : 11:00:21

The solution to this is undoubtedly straight forward but I'm tearing my hair out.

My query..
select table_no, open_time, open_period
from bar_tables
where date = required_date

gives me...
1, 10, 2
1, 13, 5
1, 20, 8
1, 30, 10
2, 14, 7
3, 10, 8
3, 20, 15
3, 50, 10

(a different number of rows for each table_no)

I need to get to...
1, 10, 2, 13, 5, 20, 8, 30, 10
2, 14, 7
3, 10, 8, 20, 15, 50, 10

(one row for each table_no with a variable no. of colums - though I'm happy to pad the end columns with zeros).

Many thanks for any input!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 11:04:32

Search for Crosstabs query in this forum.There are millions of examples.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 11:35:42

one method


SELECT DISTINCT table_no,STUFF((SELECT ','+ CAST(open_time AS varchar(10)) + CAST(open_period AS varchar(10))  FROM Table WHERE table_no=t.table_no FOR XML PATH('')),1,1,'') AS Val
FROM Table t

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 11:53:50

Does it work in SQL 2000? I don't think so.

sfletcher
Starting Member

6 Posts

Posted - 2009-01-29 : 12:05:02

I couldn't get the STUFF approach to work. I have however found a solution using @temp tables. Not ideal, but it works and gives me results in 3 seconds.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 12:16:03

quote:
Originally posted by sodeep

Does it work in SQL 2000? I don't think so.

nope it wont. it works only from sql 2005.
didnt realise this is 2000 forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 12:24:37

see this
http://www.projectdmx.com/tsql/rowconcatenate.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 14:34:30

quote:
(one row for each table_no with a variable no. of colums - though I'm happy to pad the end columns with zeros).

Or Are you looking for this one:

Select table_no,
Max(Case when open_time = 10 then open_time else 0 end)as opentime1,
Max(Case when open_time = 10 then open_period else 0 end)as openperiod1,
Max(Case when open_time = 13 then open_time else 0 end)as opentime2,
Max(Case when open_time = 13 then open_period else 0 end)as openperiod2,
Max(Case when open_time = 20 then open_time else 0 end)as opentime3,
Max(Case when open_time = 20 then open_period else 0 end)as openperiod3,
Max(Case when open_time = 30 then open_time else 0 end)as opentime4,
Max(Case when open_time = 30 then open_period else 0 end)as openperiod4,
Max(Case when open_time = 14 then open_time else 0 end)as opentime5,
Max(Case when open_time = 14 then open_period else 0 end)as openperiod5,
Max(Case when open_time = 50 then open_time else 0 end)as opentime6,
Max(Case when open_time = 50 then open_period else 0 end)as openperiod6
from table
Group by table_no

Look at dynamic Crosstabs

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 15:00:43

[code]
--Sample table variable
Declare @t table
(table_no int, open_time int, open_period int)

--Preparing Sample Data
Insert @t
Select 1, 10, 2 union all
Select 1, 13, 5 union all
Select 1, 20, 8 union all
Select 1, 30, 10 union all
Select 2, 14, 7 union all
Select 3, 10, 8 union all
Select 3, 20, 15 union all
Select 3, 50, 10

Select table_no,
Max(Case when seq =1 then convert(varchar(10),open_time)+ ',' + convert(varchar(10),open_period) else '' end)+ ',' +
Max(Case when seq =2 then convert(varchar(10),open_time)+ ',' + convert(varchar(10),open_period) else '' end)+ ',' +
Max(Case when seq =3 then convert(varchar(10),open_time)+ ',' + convert(varchar(10),open_period) else '' end)+ ',' +
Max(Case when seq =4 then convert(varchar(10),open_time)+ ',' + convert(varchar(10),open_period) else '' end)as Variable
from
(SELECT p1.table_no,p1.open_time,p1.open_period,(Select Count(*) from @t p2 where p1.table_no = p2.table_no
and p2.open_time<=p1.open_time )as seq
from @t p1) D
Group by table_no

--Output
table_no Variable
1 10,2,13,5,20,8,30,10
2 14,7,,,
3 10,8,20,15,50,10, [/code]

sfletcher
Starting Member

6 Posts

Posted - 2009-01-30 : 04:21:21

thanks sodeep, that's a tidy solution.

however..
I think the output has to be in separate fields as it's a datasource for a reporting services chart. Also the table_no may be any number, or indeed characters so there's no way I can hard code for every possibility!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 09:17:41

quote:
Originally posted by sfletcher

thanks sodeep, that's a tidy solution.

however..
I think the output has to be in separate fields as it's a datasource for a reporting services chart. Also the table_no may be any number, or indeed characters so there's no way I can hard code for every possibility!

Thats why I said you to search for Dynamic Crosstabs in forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources