Adding columns to a query from the results of anot

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

General SQL Server Forums

New to SQL Server Programming

Adding columns to a query from the results of anot

Author

Topic

firewall
Starting Member

2 Posts

Posted - 2014-04-14 : 23:24:12

Alright, I'll try to explain this as simple a possible.

I have a table called approval_levels in which I have 2 columns:

|LEVEL_ID |NAME |
|1 |app_level_1 |
|2 |app_level_2 |
|3 |app_level_3 |

I have a second table called requests, in which I have 3 columns:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |
|1 |wd-40 |Acme |
|2 |Windex |Acme |
|3 |Propane |Acme |

I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.

|REQUEST_ID |LEVEL_ID |
|1 |1 |
|1 |2 |
|1 |3 |
|2 |1 |
|3 |3 |

Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |app_level_1 |app_level_2 |app_level_3 |
|1 |wd-40 |Acme |X |X |X |
|2 |Windex |Acme |X | | |
|3 |Propane |Acme | | |X |

Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.

Now, how in the world do I do something like this??? I don't even know where to start looking?

Thanks for the help!

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-04-15 : 00:42:11

CREATE TABLE Table1(LEVEL_ID INT,NAME VARCHAR(20))
INSERT INTO Table1 VALUES(1,'app_level_1'),(2,'app_level_2'),(3,'app_level_3')

CREATE TABLE Table2(REQUEST_ID INT,PRODUCT_NAME VARCHAR(20),MANUFACTURER VARCHAR(10))
INSERT INTO Table2 VALUES(1,'wd-40','Acme'),(2,'Windex','Acme'),(3,'Propane','Acme')

CREATE TABLE Table3(REQUEST_ID INT,LEVEL_ID INT)
INSERT INTO Table3 VALUES(1,1),(1,2),(1,3),(2,1),(3,3)

DECLARE @Cols NVARCHAR(MAX),@Result NVARCHAR(MAX)
SET @Cols = STUFF((SELECT ','+NAME FROM Table1 FOR XML PATH('')),1,1,'')

SET @Result = N'
SELECT REQUEST_ID,PRODUCT_NAME,MANUFACTURER,'+@Cols+' FROM
(
SELECT DISTINCT T2.REQUEST_ID,T2.PRODUCT_NAME,T2.MANUFACTURER,T1.NAME,T3.LEVEL_ID
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.LEVEL_ID = T2.REQUEST_ID
INNER JOIN Table3 AS T3
ON T2.REQUEST_ID = T3.REQUEST_ID)x
PIVOT(MAX(Level_Id) FOR NAME IN('+@Cols+')) AS pvt'

EXECUTE(@Result)

u can insert another name in table1, column can be generate dynamically.

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources