Need to populate new column in the result set

Question

Post reply

Need to populate new column in the result set

Mac1986

SSCertifiable

Points: 6216
More actions
November 4, 2012 at 3:34 am

#264239

Hi I have a table which has 3 unique values as below table.
SNo. Date Cat Value
1 11/01 A 100
2 11/02 A 101
3 11/01 B 102
4 11/02 B 100
5 11/01 C 1088
5 11/02 C 1026
I would want to see the result set as
SNo. Date Cat_A Value_A Cat_B Value_B Cat_C Value_C
1 11/01 A 100 B 102 C 1088
2 11/02 A 101 B 100 C 1026
I'm getting confused how to get it ... Please help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

imex Ten Centuries Points: 1234 More actions · Answer 1

Hi,

Try:

select

a.SNo,

a.Date,

a.Cat as Cat_A,

a.Value as Value_A,

b.Cat as Cat_B,

b.Value as Value_B,

c.Cat as Cat_C,

c.Value as Value_C

from MyTable as a

left join MyTable as b

on b.Date = a.Date and b.Cat = 'B'

left join MyTable as c

on c.Date = c.Date and c.Cat = 'C'

where a.Cat = 'A'

Hope this helps.

https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

imex (11/4/2012)
Hi,
Try:
select
a.SNo,
a.Date,
a.Cat as Cat_A,
a.Value as Value_A,
b.Cat as Cat_B,
b.Value as Value_B,
c.Cat as Cat_C,
c.Value as Value_C
from MyTable as a
left join MyTable as b
on b.Date = a.Date and b.Cat = 'B'
left join MyTable as c
on c.Date = c.Date and c.Cat = 'C'
where a.Cat = 'A'
Hope this helps.

A double self-join? :w00t:

How about a single table scan instead:

DECLARE @T TABLE

(SNo INT, Date DATETIME, CAT CHAR(1), Value INT)

INSERT INTO @T (SNo, Date, Cat, Value)

SELECT 1, '2011-11-01','A',100

UNION ALL SELECT 2, '2011-11-02','A',101

UNION ALL SELECT 3, '2011-11-01','B',102

UNION ALL SELECT 4, '2011-11-02','B',100

UNION ALL SELECT 5, '2011-11-01','C',1088

UNION ALL SELECT 5, '2011-11-02','C',1026

SELECT SNo=ROW_NUMBER() OVER (ORDER BY Date)

,Date, Cat_A, Value_A, Cat_B, Value_B, Cat_C, Value_C

FROM (

SELECT Date

,Cat_A='A'

,Value_A=MAX(CASE Cat WHEN 'A' THEN Value END)

,Cat_B='B'

,Value_B=MAX(CASE Cat WHEN 'B' THEN Value END)

,Cat_C='C'

,Value_C=MAX(CASE Cat WHEN 'C' THEN Value END)

FROM @T

GROUP BY Date) a

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

Dwain,

Add 10 to each SNo and see if it still works.

You're on the right track though. Just a small change will do it.

SELECT SNo = MIN(SNo)

, Date

, Cat_A = 'A'

, Value_A = MAX(CASE Cat WHEN 'A' THEN Value END)

, Cat_B = 'B'

, Value_B = MAX(CASE Cat WHEN 'B' THEN Value END)

, Cat_C = 'C'

, Value_C = MAX(CASE Cat WHEN 'C' THEN Value END)

FROM @T

GROUP BY Date

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

Jeff Moden (11/4/2012)
Dwain,
Add 10 to each SNo and see if it still works.
You're on the right track though. Just a small change will do it.
SELECT SNo = MIN(SNo)
, Date
, Cat_A = 'A'
, Value_A = MAX(CASE Cat WHEN 'A' THEN Value END)
, Cat_B = 'B'
, Value_B = MAX(CASE Cat WHEN 'B' THEN Value END)
, Cat_C = 'C'
, Value_C = MAX(CASE Cat WHEN 'C' THEN Value END)
FROM @T
GROUP BY Date

Jeff,

I see what you did, but if I "add 10 to each SNo" in the sample data like this:

INSERT INTO @T (SNo, Date, Cat, Value)

SELECT 11, '2011-11-01','A',100

UNION ALL SELECT 12, '2011-11-02','A',101

UNION ALL SELECT 13, '2011-11-01','B',102

UNION ALL SELECT 14, '2011-11-02','B',100

UNION ALL SELECT 15, '2011-11-01','C',1088

UNION ALL SELECT 15, '2011-11-02','C',1026

Mine still gets the same results. Not intentionally being obtuse here. 😀

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

vinu512 SSCoach Points: 15729 More actions · Answer 5

That was a really nice catch Mr. Moden..........I came up with the exact same query as Dwain......never thought of the scenario that you mentioned. I guess this foresight comes with experience.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉