InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 15:47:20 GMT20http://www.sqlservercentral.com/Forums/Topic1383815-392-1.aspxGOOD idea, sure, here is the code:[code]SELECT col1, col2, Max(CASE col4 WHEN 1 THEN col3 ELSE NULL END) AS col3_1, Max(CASE col4 WHEN 2 THEN col3 ELSE NULL END) AS col3_2, Max(CASE col4 WHEN 3 THEN col3 ELSE NULL END) AS col3_3FROM table1GROUP BY col1, col2 [/code]And in case you are wondering, formatting of the above SQL was done at this page (with whom I have no affiliation with): [url]http://www.dpriver.com/pp/sqlformat.htm[/url]Tue, 13 Nov 2012 10:50:13 GMTshahgolshttp://www.sqlservercentral.com/Forums/Topic1383815-392-1.aspx[quote][b]shahgols (11/12/2012)[/b][hr]I figured it out, just like you said, I used MAX around each CASE statement and it ran like a charm. Thanks![/quote]Outstanding! Would you post your solution so that others may learn from it? Thanks!Mon, 12 Nov 2012 17:03:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383815-392-1.aspxI figured it out, just like you said, I used MAX around each CASE statement and it ran like a charm. Thanks!Mon, 12 Nov 2012 14:07:07 GMTshahgolshttp://www.sqlservercentral.com/Forums/Topic1383815-392-1.aspxForgot, I have also tried PIVOT.Mon, 12 Nov 2012 13:07:39 GMTshahgolshttp://www.sqlservercentral.com/Forums/Topic1383815-392-1.aspxThat's exactly what I am trying right now! :)I got this so far:SELECT col1, col2, CASE col4 WHEN 1 THEN col3 ELSE NULL END AS col3_1, CASE col4 WHEN 2 THEN col3 ELSE NULL END AS col3_2, CASE col4 WHEN 3 THEN col3 ELSE NULL END AS col3_3FROM table1GROUP BY col1, col2 However, when I run this I get the following errors:Msg 8120, Level 16, State 1, Line 1Column col3 is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Msg 8120, Level 16, State 1, Line 1Column col4 is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.I'm working on it, but if you have the solution, please don't hesitate to post it. Thanks for your input by the way.Mon, 12 Nov 2012 13:05:13 GMTshahgolshttp://www.sqlservercentral.com/Forums/Topic1383815-392-1.aspxWhat have you tried?a couple hints.Use the case statement, wrapped in Min, or MaxGroup by Col 1, Col 2Check this out.[url=http://www.sqlservercentral.com/articles/Best+Practices/61537/]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]Mon, 12 Nov 2012 12:54:57 GMTRay Mhttp://www.sqlservercentral.com/Forums/Topic1383815-392-1.aspxHi all,I have to make the following, but I've had no luck getting it done. Can you please help? Thanks in advance.Before:Col1 Col2 Col3 Col41 2 A 11 2 B 21 2 C 3After:Col1 Col2 Col3_1 Col3_2 Col3_31 2 A B CThe data includes only 3 rows max for combination of Col1 and Col2. And the results have to be ordered using Col4, Ascending. Thanks again.There can only beMon, 12 Nov 2012 12:22:15 GMTshahgols