April 5, 2010 at 8:06 pm
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.
April 5, 2010 at 8:25 pm
I understood partially your requirement and here is the code for that
SELECT
CASE LOOKUP.COL1
WHEN 'A' THEN DATA.A,
WHEN 'B' THEN DATA.B,
WHEN 'C' THEN DATA.C,
ELSE DATA.D
END ACTUAL_VALUE
FROM DATA
INNER JOIN LOOKUP
ON DATA.KEY = LOOKUP.KEY
I am not sure on the desired result u asked for.. so it will be very helpful for us if u could post a visual representation of your desired result.
Please tell us if the above code works..
Cheers,
C'est Pras!!
P.S : i am writing this code from my home where i have no access to SQL Server. once i reach office i will check the validity of that query.
April 5, 2010 at 8:42 pm
Looks like it works, but I made one slight change that may or may not be needed.
CREATE TABLE dbo.LOOKUP (
CHAR(2)
,COL1 CHAR(1)
)
INSERT INTO dbo.LOOKUP VALUES
('00','A'),('01','B'),('10','C'),('11','D')
CREATE TABLE dbo.DATA (
CHAR(2)
,A INT
,B INT
,C INT
,D INT
)
INSERT INTO dbo.DATA VALUES
('00',1,2,3,4),('01',5,6,7,8)
SELECT
CASE LOOKUP.COL1
WHEN 'A' THEN DATA.A
WHEN 'B' THEN DATA.B
WHEN 'C' THEN DATA.C
WHEN 'D' THEN DATA.D
ELSE null
END ACTUAL_VALUE
FROM dbo.DATA
INNER JOIN dbo.LOOKUP
ON DATA. = LOOKUP.
DROP TABLE dbo.LOOKUP;
DROP TABLE dbo.DATA;
April 5, 2010 at 8:45 pm
Thanks for the prompt reply. The query you posted would work. But, the example I provided is really a short version of what I really need.
In the actual problem, the data in COL1 is not limted to A,B,C and D. And also there is not just COL1,I also have COL2, COL3 etc in the Lookup table. I could i write the whole thing with CASE statements, it will be very very ugly.
And as for your question, My desired result would look something like this.
Key COL1ActualValue
00 1
01 6
But, ofcourse with columns like COL2ActualValue, COL3ActualValue etc if I have COL2, COL3 etc defined on the Lookup table.
Somehow I have the feeling I could accomplish this using Dynamic SQL, just can't seem to get it.
Thanks
April 5, 2010 at 8:46 pm
Thanks Lynn for checking that out!! i was kind of reluctant to post it without testing.. now that u tested and decorated the code, the OP can directly us that.. thanks
😉
April 5, 2010 at 8:51 pm
rahul.koduru (4/5/2010)
But, the example I provided is really a short version of what I really need.In the actual problem,
thats the turning point, please be specific with what your requirement is mate 🙂
Ok, now, does your Data table have some sort of primary key to uniquely identify each row?
April 5, 2010 at 8:56 pm
Sorry about that. yes, I do have primary key on the DATA table.
We can call it Id
CREATE TABLE DATA (
ID INT IDENTITY(1,1)
KEY CHAR(2)
,A INT
,B INT
,C INT
,D INT
)
INSERT INTO DATA VALUES
('00',1,2,3,4),('01',5,6,7,8)
April 5, 2010 at 9:01 pm
May I suggest that you provide the full requirements of what you are attempting to do as well as any code you have already tried writing to solve your problem?
With what you have provided, it is difficult to really put together code that will help you sove your problem.
May I suggest reading the first article I have referenced in my signature block below? Besure to also post your expected results based on sample data. This should look like the output of the query we are attempting to help you write.
April 5, 2010 at 9:33 pm
Lynn,
Sorry. I'm a rookie. Below is the complete question and 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 desired result is as below.
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
To obtain the above result, I have to lookup the key from Data Table, Go to the Lookup table and find out the corresponding column names (either A,B,C or D) for Col1, Col2, Col3 and then retrieve the underlying data accordingly. And, i need to do this without CASE statements.
Thanks.
April 5, 2010 at 9:38 pm
Two questions.
One, why does it have to be dynamic?
Two, why eliminate CASE statements when they may be appropriate?
April 5, 2010 at 9:48 pm
Lynn,
It doesn't have to be dynamic. I can't really post the exact question due to compliance issues. In my example, My Data table contains only A,B,C and D columns. But what if there are lot more columns like E,F,G,H etc. The CASE statement would get bigger and bigger. Also my Lookup table in the example has only Col1, Col2 and Col3. Again if I have more columns like Col4,Col5 etc. You could see how quickly the CASE statement code would turn ugly (bigger) and hard to maintain.
Thanks.
Rahul
April 5, 2010 at 10:20 pm
And I am seeing how nasty and complex it could get using dynamic sql as well. Although case statments may result in a long query, it may be the better alternative to dynamic sql.
April 5, 2010 at 10:40 pm
I agree with Lynn here... CASE statements look appropriate here and to my knowledge readable too, then why would you prefer dynamic SQL??
here is a sample code with CASE statement that looked readable as well as producing your desired result
SELECT DATA.ID,
CASE look_up.COL1
WHEN 'A' THEN DATA.A
WHEN 'B' THEN DATA.B
WHEN 'C' THEN DATA.C
WHEN 'D' THEN DATA.D
ELSE null
END COL1_ACTUAL_VALUE ,
CASE look_up.COL2
WHEN 'A' THEN DATA.A
WHEN 'B' THEN DATA.B
WHEN 'C' THEN DATA.C
WHEN 'D' THEN DATA.D
ELSE null
END COL2_ACTUAL_VALUE ,
CASE look_up.COL3
WHEN 'A' THEN DATA.A
WHEN 'B' THEN DATA.B
WHEN 'C' THEN DATA.C
WHEN 'D' THEN DATA.D
ELSE null
END COL3_ACTUAL_VALUE
FROM dbo.DATA
INNER JOIN dbo.look_up
ON DATA.[Key_Col] = look_up.[Key_Col]
And BTW, thanks for posting all the necessary table structures and sample data..
April 6, 2010 at 6:01 am
Oh man, i very nearly pulled this thing out without CASE and DYNAMIC-SQL...
here is the code; if in future u are planning to append many extra columns, say e,f,g etc,all u need to do is to add the column name in the section below the comments...
;WITH Look_Up_Keys (ID, Col1_Val,Col2_Val,Col3_Val)
AS
(
SELECT D.ID, L.COL1, L.COL2,L.COL3
FROM dbo.DATA D
INNER JOIN dbo.look_up L
ON D.[Key_Col] = L.[Key_Col]
)
,
UNPIVOTED(ID, COL_VALUES, KEYCOL)
AS
(
SELECT
ID, COL_VALUES, KEYCOL
FROM
/* You will just be needed to added the extra alphabets..
..(in your case the actual column names in DATA table)..
.. below */
(SELECT ID,A,B,C,D FROM DATA) INNERPIVOT
UNPIVOT
/* You will just be needed to added the extra alphabets..
..(in your case the actual column names in DATA table)..
.. below */
(KEYCOL FOR COL_VALUES IN (A,B,C,D)) OUTERPIVOT
)
,
FINAL_TABLE (ID, COL_VALS) AS
(
SELECT UNPIVOTED.ID , UNPIVOTED.KEYCOL
FROM Look_Up_Keys
INNER JOIN UNPIVOTED
ON Look_Up_Keys.ID = UNPIVOTED.ID
AND (Look_Up_Keys.Col1_Val = UNPIVOTED.COL_VALUES OR
Look_Up_Keys.Col2_Val = UNPIVOTED.COL_VALUES OR
Look_Up_Keys.Col3_Val = UNPIVOTED.COL_VALUES)
)
SELECT [ID], [Col1_ActualValue],[Col2_ActualValue],[Col3_ActualValue]
FROM (SELECT 'Col'+CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS VARCHAR)+'_ActualValue' AS ROW_ID , ID, COL_VALS
FROM FINAL_TABLE) ipt
PIVOT (MAX(COL_VALS) FOR ROW_ID in ([Col1_ActualValue],[Col2_ActualValue],[Col3_ActualValue])) pvt
BUT BUT BUT, there is a catch here... if ID 3 has to retreive values from C,D,A columns, the above does that sharply, but it misplaces the three values..
that is, the C,D and A values which must be Col1, Col2, Col3 respectively, are ordered by (SQL takes care of itself, i am not doing anything in the code :unsure: ) and the results are juggled as in 'A' goes to 'Col1' & 'C' goes to 'Col2' & 'D' goes to 'Col3'..
This code above is not a high-performer... just a heads-up for u to go in a direction..
SQL Gurus, please take a look at this code and tel me where i went wrong ?? :crying:
April 6, 2010 at 7:14 am
Why this data model?
Are you stuck with it?
Because this does not sound like a relational model.
I suggest that you re-think the data model and make it relational.
Many of your coding issues may just go away once you do that.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply