InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 17:35:51 GMT20http://www.sqlservercentral.com/Forums/Topic791603-5-1.aspx[quote][b]arun.sas (9/22/2009)[/b][hr]Use the UNION to get correct result.[/quote]Ummmm... maybe not... UNION will get rid of any duplicates and the duplicates may be important.Tue, 22 Sep 2009 18:33:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic791603-5-1.aspx[quote][b]Dave Ballantyne (9/22/2009)[/b][hr]Simple enough with a union[code]select Job ,Name1_1 ,city1_1 , phone1_1from tableunion all select Job ,Name1_2 ,city1_2 , phone1_2from tableunion all select Job ,Name1_3 ,city1_3 , phone1_3from tableunion all select Job ,Name1_4 ,city1_4 , phone1_4from table[/code][/quote]Hi,Use the UNION to get correct result.create table #temp(jobs varchar(10),colA1 varchar(10),colB1 varchar(10),colC1 varchar(10),colA2 varchar(10),colB2 varchar(10),colC2 varchar(10),colA3 varchar(10),colB3 varchar(10),colC3 varchar(10)) insert into #tempselect 'XYZ','aaaa','abcd','1111','bbbb','bcda','2222','cccc','cdab','3333'union allselect 'YZA','aaaa','abcd','1111','bbbb','bcda','2222','cccc','cdab','3333'union allselect 'ZAB','','','','','','','','',''[code]select jobs ,colA1 ,colB1 , colC1from #tempunion select jobs ,colA2 ,colB2 , colC2from #tempunion select jobs ,colA3 ,colB3 , colC3from #temp[/code]RESULTjobs colA1 colB1 colC1XYZ aaaa abcd 1111XYZ bbbb bcda 2222XYZ cccc cdab 3333YZA aaaa abcd 1111YZA bbbb bcda 2222YZA cccc cdab 3333ZAB[code]select jobs ,colA1 ,colB1 , colC1from #tempunion allselect jobs ,colA2 ,colB2 , colC2from #tempunion allselect jobs ,colA3 ,colB3 , colC3from #temp[/code]RESULTjobs colA1 colB1 colC1XYZ aaaa abcd 1111YZA aaaa abcd 1111ZAB XYZ bbbb bcda 2222YZA bbbb bcda 2222ZAB XYZ cccc cdab 3333YZA cccc cdab 3333ZAB Tue, 22 Sep 2009 04:41:40 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic791603-5-1.aspxSimple enough with a union[code]select Job ,Name1_1 ,city1_1 , phone1_1from tableunion all select Job ,Name1_2 ,city1_2 , phone1_2from tableunion all select Job ,Name1_3 ,city1_3 , phone1_3from tableunion all select Job ,Name1_4 ,city1_4 , phone1_4from table[/code]Tue, 22 Sep 2009 03:58:08 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic791603-5-1.aspxI need a query / procedure which would convert Columns into Rows without causing any damages to the original data.Table#1Job Name1_1 name1_2 name1_2 name1_4 city1_1 city1_2 city1_3 city1_4 phone1_1 phone1_2 phoen1_3 phone1_4 XYZ aaaa bbbb cccc dddd abcd bcde cdef defg 1111 2222 3333 4444output table as:Job name city phone XYZ aaaa abcd 1111 XYZ bbbb bcde 2222 XYZ cccc cdef 3333 XYZ dddd defg 4444Please help .Tue, 22 Sep 2009 03:25:42 GMTl_srinuu