Author |
Topic |
rishabhgairola
Starting Member
2 Posts |
Posted - 2012-11-20 : 00:46:47
|
i have a table named ColumnField like this :-ID L1 L2 L3 L4 L5 L6 L7 L8 L9 L101 1 3 4 4 7 2 5 2 3 22 2 7 5 3 4 3 2 3 1 13 3 4 6 4 2 4 4 4 2 44 4 3 2 5 7 2 6 5 1 55 5 2 3 6 1 4 7 7 4 7 and another table named ColumnText like this :-TextID Text1 aerosol2 hydro3 solar4 nuclear5 lunar6 tidal7 windI need to generate a table where all the fields L1,L2,L3...L10 are replaced with their corresponding texts. Also, the numbers under the various coulmns in the columnField table (L1,L2...L10) correspond to the TextID of the ColumnText table.Thanks in advance. |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-20 : 01:06:13
|
untested but this should work:select t1.text as L1 ,t2.text as L2 ,etc...from ColumnField fjoin ColumnText t1 on t1.textid = f.L1join columnText t2 on t2.textid = f.L2etc... Be One with the OptimizerTG |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-20 : 01:16:53
|
Hi TG, your solution is correctdeclare @ColumnField table (ID int, L1 int, L2 int, L3 int, L4 int, L5 int, L6 int, L7 int, L8 int, L9 int, L10 int)insert into @ColumnFieldselect 1, 1, 3, 4, 4, 7, 2, 5, 2, 3, 2 union allselect 2, 2, 7, 5, 3, 4, 3, 2, 3, 1, 1 union allselect 3, 3, 4, 6, 4, 2, 4, 4, 4, 2, 4 union allselect 4, 4, 3, 2, 5, 7, 2, 6, 5, 1, 5 union allselect 5, 5, 2, 3, 6, 1, 4, 7, 7, 4, 7declare @ColumnText table(TextID int, [Text] varchar(20))insert into @ColumnTextselect 1, 'aerosol' union allselect 2, 'hydro' union allselect 3, 'solar' union allselect 4, 'nuclear' union allselect 5, 'lunar' union allselect 6, 'tidal' union allselect 7, 'wind'select f.id ,t1.text as L1 ,t2.text as L2 ,t3.text as L3 ,t4.text as L4 ,t5.text as L5 ,t6.text as L6 ,t7.text as L7 ,t8.text as L8 ,t9.text as L9 ,t10.text as L10from @ColumnField fjoin @ColumnText t1 on t1.textid = f.L1join @ColumnText t2 on t2.textid = f.L2join @ColumnText t3 on t3.textid = f.L3join @ColumnText t4 on t4.textid = f.L4join @ColumnText t5 on t5.textid = f.L5join @ColumnText t6 on t6.textid = f.L6join @ColumnText t7 on t7.textid = f.L7join @ColumnText t8 on t8.textid = f.L8join @ColumnText t9 on t9.textid = f.L9join @ColumnText t10 on t10.textid = f.L10--Chandu |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-20 : 01:20:35
|
Thanks for doing the heavy lifting, Chandu! Be One with the OptimizerTG |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-20 : 02:18:21
|
[code]SELECT ID,[L1],[L2],...,[L10]FROM(SELECT ID,m.Cat,n.TextFROM(SELECT ID,Cat,ValFROM ColumnFieldUNPIVOT (Val FOR Cat IN ([L1],[L2],[L3],...,[L10]))u)mINNER JOIN ColumnText nON n.TextID = m.Val)pPIVOT(MAX(Text) FOR Cat IN ([L1],[L2],...,[L10]))q[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|