Author |
Topic |
guiz
Starting Member
7 Posts |
Posted - 2013-01-28 : 03:17:40
|
Hi, All. Could you, please, help me with sql query ? For example, if I have a table1 like the following(fields/values separated by commas):component, "bin", "prd"2.1 , "1", "217"6.5 , "4", "217"7.1 ,"3", "217"7.6 ,"5", "217"7.7 ,"5", "217"1.3 ,"2", "217"1.1 ,"1", "298"3.1 ,"1", "298"6.2 ,"2", "298"7.3 ,"5", "298"8.1 ,"3", "298"8.4 ,"4", "298"1.1 ,"5", "298"and I want to produce a table2 below:prd , combo217 , 2.1|1.3|7.1|6.5|7.6217 , 2.1|1.3|7.1|6.5|7.7298 , 1.1|6.2|8.1|8.4|1.1298 , 1.1|6.2|8.1|8.4|7.3298 , 3.1|6.2|8.1|8.4|1.1298 , 3.1|6.2|8.1|8.4|7.3whereas column 'combo' contains all possible unique combinations of components in table1 written in ascending order of table1 'bin' values.Thank you in advance as your help is very much appreciated.Cheers,guiz |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-28 : 03:34:58
|
[code]SELECT prd,STUFF((SELECT '|' + CAST(component AS varchar(10) FROM table1 WHERE prd = t.prd ORDER BY [bin] FOR XML PATH('')),1,1,'') AS comboFROM (SELECT DISTINCT prd FROM table1)t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|