how to present the data comparison

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

how to present the data comparison

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2013-09-10 : 11:50:08

Hi there,

A table with data like this:
Id | Type | val1 | val2 ...
12 | B3 | 21.9 | 32.5
12 | C2 | 12.4 | 21.3
...
13 | B3 | 19.2 | 11.4
13 | C2 | 32.5 |61.2

Now I need show the data this way:
| Type | val1 | val1 | val2 | val2 ...
| B3 | 21.9 | 19.2 | 32.5 | 11.4
| C2 | 12.4 | 32.5 | 21.3 | 61.2
The tricky part is the weekId 13 data must be arranged on the same line with weekId 12. Possible?
Thanks in advance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-10 : 12:28:38

[code]SELECT
[Type],
MAX(CASE WHEN Id = 12 THEN val1 END) AS val1,
MAX(CASE WHEN id = 13 THEN val1 END) AS val1,
MAX(CASE WHEN Id = 12 THEN val2 END) AS val2,
MAX(CASE WHEN id = 13 THEN val2 END) AS val2
FROM
Tbl
GROUP BY
[Type][/code]

allan8964
Posting Yak Master

249 Posts

Posted - 2013-09-10 : 13:03:11

Beautiful!!! Thank you so much James!
But I don't understand what does MAX() here do.

allan8964
Posting Yak Master

249 Posts

Posted - 2013-09-10 : 13:08:18

Tried something and actually MAX() here does not need get maximum or minimum values but just make the case when statement work. So if you change it to MIN() you get same results. Is it right? Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-10 : 13:24:50

quote:
Originally posted by allan8964

Tried something and actually MAX() here does not need get maximum or minimum values but just make the case when statement work. So if you change it to MIN() you get same results. Is it right? Thanks.

Yes - the MAX or MIN (or any aggregate function that is applicable to the data type) can work. The assumption is that there will be only one value that satisfies the grouping condition for a given Id and Type, so you are not losing any data, but it allows you to group by the Type column.

allan8964
Posting Yak Master

249 Posts

Posted - 2013-09-10 : 15:29:03

Got it, thanks again!!!

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-09-16 : 10:05:40

Another Way :
Select * from
(select Id,Type,val1,val2 from table1)K
PIVOT (MAX(TYPE) (ID IN ([val1],[val2],[val1],[val2]))P

P.V.P.MOhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources