A question on Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

A question on Dynamic SQL

Author

Topic

rahul_koduru
Starting Member

3 Posts

Posted - 2010-04-05 : 21:59:54

Hi,
I have the following tables.

CREATE TABLE LOOKUP (
KEY CHAR(2)
,COL1 CHAR(1)
)

INSERT INTO LOOKUP VALUES
('00','A'),('01','B'),('10','C'),('11','D')

CREATE TABLE DATA (
KEY CHAR(2)
,A INT
,B INT
,C INT
,D INT
)

INSERT INTO T2 VALUES
('00',1,2,3,4),('01',5,6,7,8)

I have to lookup each key from DATA table in the LOOKUP table and pick Columns A,B,C or D according to it to get my actual data. Somebody suggested I could do this using Dynamic SQL. Any help would be appreciated.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-05 : 22:43:10

where is DDL for table T2 ? Is it DATA ?

quote:

I have to lookup each key from DATA table in the LOOKUP table and pick Columns A,B,C or D according to it to get my actual data

Can you explain more on this ?

Can you also post the required result ?

KH
[spoiler]Time is always against us[/spoiler]

rahul_koduru
Starting Member

3 Posts

Posted - 2010-04-06 : 11:43:31

Hi, Im sorry here is the whole code.



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);

My result should look like this



Id Col1ActualValue Col2ActualValue Col3ActualValue
1  1                     2                    3
2  2                     3                    4 
3  3                     4                    1
4  1                     2                    4
5  5                     6                    7
6  6                     7                    8
7  7                     8                    5
8  5                     6                    8

I want to do this without using CASE statements.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 12:27:13

why use dynamic sql for this?


CREATE TABLE [Lookup] (
 Id INT IDENTITY (1,1),
   [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);


SELECT m.Id,
MAX(CASE WHEN l.Col1=m.Cat THEN m.Val ELSE NULL END) AS Col1Val,
MAX(CASE WHEN l.Col2=m.Cat THEN m.Val ELSE NULL END) AS Col2Val,
MAX(CASE WHEN l.Col3=m.Cat THEN m.Val ELSE NULL END) AS Col3Val
FROM [Lookup] l
INNER JOIN (SELECT [Key],Cat,Val,Id
            FROM (SELECT Id,[Key],A,B,C,D
                  FROM Data)d
            UNPIVOT ( Val FOR Cat IN (A,B,C,D))u
           )m
ON m.[Key]= l.[Key]
GROUP BY l.[Key],m.Id

DROP TABLE [Lookup]
DROP TABLE Data


output
-----------------------------------------
Id	Col1Val	Col2Val	Col3Val
1	1	2	3
2	2	3	4
3	3	4	1
4	1	2	4
5	5	6	7
6	6	7	8
7	7	8	5
8	5	6	8

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources