InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 01:15:51 GMT20http://www.sqlservercentral.com/Forums/Topic1380778-392-1.aspxThat 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.Tue, 06 Nov 2012 02:53:30 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1380778-392-1.aspx[quote][b]Jeff Moden (11/4/2012)[/b][hr]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.[code="sql"] 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[/code][/quote]Jeff,I see what you did, but if I "add 10 to each SNo" in the sample data like this:[code="sql"]INSERT INTO @T (SNo, Date, Cat, Value)SELECT 11, '2011-11-01','A',100UNION ALL SELECT 12, '2011-11-02','A',101UNION ALL SELECT 13, '2011-11-01','B',102UNION ALL SELECT 14, '2011-11-02','B',100UNION ALL SELECT 15, '2011-11-01','C',1088UNION ALL SELECT 15, '2011-11-02','C',1026[/code]Mine still gets the same results. Not intentionally being obtuse here. :-DSun, 04 Nov 2012 21:05:49 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1380778-392-1.aspxDwain,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.[code="sql"] 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[/code]Sun, 04 Nov 2012 20:50:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1380778-392-1.aspx[quote][b]imex (11/4/2012)[/b][hr]Hi,Try:[code="sql"]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_Cfrom MyTable as aleft 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'[/code]Hope this helps.[/quote]A double self-join? :w00t:How about a single table scan instead:[code="sql"]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',100UNION ALL SELECT 2, '2011-11-02','A',101UNION ALL SELECT 3, '2011-11-01','B',102UNION ALL SELECT 4, '2011-11-02','B',100UNION ALL SELECT 5, '2011-11-01','C',1088UNION ALL SELECT 5, '2011-11-02','C',1026SELECT SNo=ROW_NUMBER() OVER (ORDER BY Date) ,Date, Cat_A, Value_A, Cat_B, Value_B, Cat_C, Value_CFROM ( 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[/code]Sun, 04 Nov 2012 17:40:30 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1380778-392-1.aspxHi,Try:[code="sql"]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_Cfrom MyTable as aleft 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'[/code]Hope this helps.Sun, 04 Nov 2012 06:04:13 GMTimexhttp://www.sqlservercentral.com/Forums/Topic1380778-392-1.aspxHi I have a table which has 3 unique values as below table.SNo. Date Cat Value1 11/01 A 1002 11/02 A 1013 11/01 B 1024 11/02 B 1005 11/01 C 10885 11/02 C 1026I would want to see the result set asSNo. Date Cat_A Value_A Cat_B Value_B Cat_C Value_C1 11/01 A 100 B 102 C 10882 11/02 A 101 B 100 C 1026I'm getting confused how to get it ... Please helpSun, 04 Nov 2012 03:34:27 GMTMac1986