Help with sql 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

General SQL Server Forums

New to SQL Server Programming

Help with sql query

Author

Topic

jcarrallo
Starting Member

35 Posts

Posted - 2014-05-13 : 11:11:57

Hello
I have a table with the following structure (3 fields: name, date and value):

Name * Date * Value
Peter * 01/01/14* 100
Peter * 01/02/14* 210
Peter * 01/03/14* 430
Frederic* 01/01/14* 210
Frederic* 01/02/14* 190
Frederic* 01/03/14* 320

And I would like to do a query so that I get the following three columns (date, value1 and value2)

Date * Value1 *Value2
01/01/14 *100* 210
01/02/14 *210* 190
01/03/14 *430* 320

Could you please help me how to do it?
thanks in advanced
Jay

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-13 : 11:45:59

[code]

declare @t table (Name varchar(50), [Date] datetime, Value int)
insert into @t values
('Peter', '01/01/14', 100),
('Peter', '01/02/14', 210),
('Peter', '01/03/14', 430),
('Frederic', '01/01/14', 210),
('Frederic', '01/02/14', 190),
('Frederic', '01/03/14', 320)

select convert(varchar(8),[Date], 10) as 'Date', max(v.Value) as 'Value1 Value2'
from @t t1
cross apply (
select stuff(str(Value),1,1,'')
from @t t2
WHERE t1.Date = t2.Date
for xml path ('')
)v(Value)
group by [Date]
[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-13 : 11:51:59

or:


with myTable (Name, Date, Value) as
(
       select 'Peter', '01/01/14', 100 union all
       select 'Peter', '01/02/14', 210 union all
       select 'Peter', '01/03/14', 430 union all
       select 'Frederic', '01/01/14', 210 union all
       select 'Frederic', '01/02/14', 190 union all
       select 'Frederic', '01/03/14', 320
)

select p.Date, p.Peter as Value1, p.Frederic as Value2
from myTable
pivot (max(value) for [name] in ([Peter],[Frederic])) as p

output:

Date     Value1      Value2
-------- ----------- -----------
01/01/14 100         210
01/02/14 210         190
01/03/14 430         320

Be One with the Optimizer
TG

jcarrallo
Starting Member

35 Posts

Posted - 2014-05-13 : 14:55:17

This is awesome.
thanks very much.
Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources