Have anyone try on this? - 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)

Have anyone try on this?

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-22 : 21:17:41

Based on the article of robvolk: http://sqlteam.com/item.asp?ItemID=2955

Has anyone try to pivot this kind of data?


select tbl.*
into SampleData
from (
	select 1as ItemCode, 1 as [No], 'aaa' as data
	union all
	select 1, 2, 'abc'
	union all
	select 1, 3, 'abd'
	union all
	select 1, 4, 'aac'
	union all
	select 1, 5, 'ccvc'
	) as tbl

select * from SampleData
drop table SampleData

Desired result should be...


SELECT [Item Code] = '1'
	 , [1] = 'aaa'
	 , [2] = 'abc'
	 , [3] = 'abd'
	 , [4] = 'aac'
	 , [5] = 'ccvc'

Item Code  1    2    3    4    5    
---------  ---- ---- ---- ---- ---- 
1          aaa  abc  abd  aac  ccvc

(1 row(s) affected)

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 00:02:01

No. Not really.

Sorry, but what is the problem you face ?

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-23 : 02:01:34

I want to pivot the actual data without summing or counting it up.

Any solution? tnx in advance.

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 02:51:54

This should do the job.

select 	ItemCode,
	max(case when No = 1 then data else null end) as [1],
	max(case when No = 2 then data else null end) as [2],
	max(case when No = 3 then data else null end) as [3],
	max(case when No = 4 then data else null end) as [4],
	max(case when No = 5 then data else null end) as [5]
from	SampleData
group by ItemCode

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-23 : 04:03:30

:-) tnx khtan!

How about making it dynamic?
This query will not work if i have an instances of ItemCode or No.

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 04:41:10

"How about making it dynamic? "
Then you have to use Dynamic SQL. Use sp_executesql to do this.

"This query will not work if i have an instances of ItemCode or No."
Can you explain more on this ? Don't quite get what you mean.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-23 : 04:59:53

>>How about making it dynamic?

What happnes when there are thousands of rows? Do you want to show as many columns?

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-23 : 05:04:39

Yup! madhivanan. exactly.

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-23 : 05:57:29

quote:
Originally posted by jonasalbert20

Yup! madhivanan. exactly.

Want Philippines to become 1st World COuntry? Go for World War 3...

Why do you want to do that?

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-23 : 06:12:21

Tnx for the follow up question.

I want to show the Remarks(data) of every date(No) in a column per ItemCode.

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-23 : 06:18:32

See if you want something like this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-23 : 06:35:54

tnx for that link madhivan. I fetch some new idea using the link you provided.

But that was not the result set that i want.
Desired result should be:


SELECT [Item Code] = '1'
	 , [1] = 'aaa'
	 , [2] = 'abc'
	 , [3] = 'abd'
	 , [4] = 'aac'
	 , [5] = 'ccvc'


Item Code  1    2    3    4    5    
---------  ---- ---- ---- ---- ---- 
1          aaa  abc  abd  aac  ccvc

I want to show the Remarks(data) of every date(No) in a column per ItemCode.

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-23 : 09:03:12

There is no point in showing all values as columns. Either concatenate or show summarised data

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-06-24 : 04:26:11

Tnx for the opinion madhivanan.

Actually my purpose of showing that kind of data is to display the dates(No) in columnar(in columns) with corresponding remarks(data) on each date by ItemCode. It is filtered by specifying date range.

By the way i alread got it using robvolk same stored procedure.


select tbl.*
into SampleData
from (
	select 1as ItemCode, 1 as [No], 'aaa' as data
	union all
	select 1, 2, 'abc'
	union all
	select 1, 3, 'abd'
	union all
	select 1, 4, 'aac'
	union all
	select 1, 5, 'ccvc'
	) as tbl

select * from SampleData

exec crosstab 'select ItemCode from SampleData group by ItemCode'
		, 'max(data)'
		, 'No'
		, 'SampleData'

drop table SampleData

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources