Show data differently - 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

General SQL Server Forums

New to SQL Server Programming

Show data differently

Author

Topic

BUser
Starting Member

11 Posts

Posted - 2010-10-18 : 04:06:28

There are 4 columns in the table.

Col1 Col2 Col3 Col4

1/1/2010 Cotton 0.1 12
2/1/2010 Soy Beans 0.33 7 2/1/2010 Rice(M) 33.2 8.6
2/1/2010 Rice(R) 4.1 44.6
3/1/2010 Rice(M) 2.3 5.5
4/1/2010 Corn 0.4 3.2
4/1/2010 Soy Beans (big) 5.3 8.8

Now, what I need to look at is as follows

1/1/2010 2/1/2010 3/1/2010 4/1/2010

Cotton 0.1,12
Soy Beans 0.33,7
Rice(R) 33.2,8.6
Rice(M) 4.1, 44.6 2.3, 5.5
Corn 0.4,3.2
Soy Beans(Big) 5.3,8.8

So, can some show how do this one..they call by name, Pivot Query but I dont unsderstand as what to do.
Can some help ?

Sachin.Nand

2937 Posts

Posted - 2010-10-18 : 04:21:16

How many values of dates are you going to have?

PBUH

BUser
Starting Member

11 Posts

Posted - 2010-10-18 : 04:31:05

Sachin.Nand,

This is a monthly data reportes for 30 days. The owner wants data across as I show. There is second problem too. The list of grains is long too sometimes over 20 grain types.
This is a feed shop and we run sql server 2000.

THank you for your help!

BUser
Starting Member

11 Posts

Posted - 2010-10-18 : 10:37:19

can someone experts please anwser to the query ?

shaggy
Posting Yak Master

248 Posts

Posted - 2010-10-18 : 11:17:36

try this

declare @t table (col1 datetime)

insert @t

select '1/1/2010'
union
select '2/1/2010'
union
select '2/1/2010'
union
select '3/1/2010'
union
select '4/1/2010'
union
select '4/1/2010'

declare @tmp varchar(8000)

set @tmp = ''
select @tmp = @tmp + ' '+ isnull(convert(varchar(8000),col1,101),'') from @t

select @tmp

shaggy
Posting Yak Master

248 Posts

Posted - 2010-10-18 : 11:19:35

declare @t table (col1 datetime)

insert @t

select '1/1/2010'
union all
select '2/1/2010'
union all
select '2/1/2010'
union all
select '3/1/2010'
union all
select '4/1/2010'
union all
select '4/1/2010'

declare @tmp varchar(8000)

set @tmp = ''
select @tmp = @tmp + ' '+ isnull(convert(varchar(8000),col1,101),'') from @t
group by col1

select @tmp

shaggy
Posting Yak Master

248 Posts

Posted - 2010-10-18 : 11:20:43

sorry use pivot for this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources