Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to populate new column in the result set Expand / Collapse
Author
Message
Posted Sunday, November 04, 2012 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 01, 2014 5:00 PM
Points: 193, Visits: 621
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
Post #1380778
Posted Sunday, November 04, 2012 6:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 6:54 AM
Points: 40, Visits: 168
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.


http://www.imoveisemexposicao.com.br
Post #1380786
Posted Sunday, November 04, 2012 5:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
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?

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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1380854
Posted Sunday, November 04, 2012 8:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1380861
Posted Sunday, November 04, 2012 9:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1380862
Posted Tuesday, November 06, 2012 2:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
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
Post #1381461
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse