April 6, 2010 at 7:31 am
David, that was not my requirement, the OP has asked for it 🙂 i tried my level best to produce a result without case and dynamic sql 🙂
April 6, 2010 at 1:14 pm
ColdCoffee,
Your solution works except for the glitch you mentioned. Thanks for all your help. I came up with the solution I needed. Here's the code.
-- Input
CREATE TABLE #Lookup (
[Key] CHAR (2)
,Col1 CHAR(1)
,Col2 CHAR(1)
,Col3 CHAR(1)
,CONSTRAINT pk_Lookup PRIMARY KEY([Key])
);
INSERT INTO #Lookup VALUES
('00','A','B','C'),('01','B','C','D'),('10','C','D','A'),('11','A','B','D');
CREATE TABLE #Data (
Id INT IDENTITY (1,1)
,[Key] CHAR(2)
,A INT
,B INT
,C INT
,D INT
,CONSTRAINT pk_Data PRIMARY KEY(Id)
);
INSERT INTO #Data VALUES
('00',1,2,3,4),('01',1,2,3,4),('10',1,2,3,4),('11',1,2,3,4),
('00',5,6,7,8),('01',5,6,7,8),('10',5,6,7,8),('11',5,6,7,8);
-- Solution
DECLARE @sql NVARCHAR(MAX) = 'SELECT Id, ' + CHAR(10);
DECLARE @X NVARCHAR(MAX) = 'CASE [Key] ';
SELECT @X += 'WHEN ''' + [Key] + ''' THEN ' + QUOTENAME(Col1) + CHAR(10) FROM #Lookup WITH (NOLOCK);
SET @X += 'END "Col1ActualValue"' + CHAR(10);
SET @sql += @X + ',' + CHAR(10);
SET @X = 'CASE [Key] ';
SELECT @X += 'WHEN ''' + [Key] + ''' THEN ' + QUOTENAME(Col2) + CHAR(10) FROM #Lookup WITH (NOLOCK);
SET @X += 'END "Col2ActualValue"' + CHAR(10);
SET @sql += @X + ',' + CHAR(10);
SET @X = 'CASE [Key] ';
SELECT @X += 'WHEN ''' + [Key] + ''' THEN ' + QUOTENAME(Col3) + CHAR(10) FROM #Lookup WITH (NOLOCK);
SET @X += 'END "Col3ActualValue"' + CHAR(10);
SET @sql += @X + CHAR(10);
SET @sql += 'FROM #Data WITH (NOLOCK);';
-- PRINT(@SQL)
EXEC(@SQL);
The above code need not be changed when more columns like E,F,G,H etc are added to the Data table. And when you have more Columns like Col4,Col5,Col6 etc on the Lookup table, you have to add a block of code (5 lines) for each column.
Any comments on the code are appreciated.
Thanks everyone for all your help.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply