select columns from a column is a table

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

Transact-SQL (2008)

select columns from a column is a table

Author

Topic

dineshreddy
Starting Member

7 Posts

Posted - 2011-03-18 : 13:43:18

I have to select the columns in table2 dynamically from c1 in Table1

Table1
c1 c2 c3
--------------------
col1
col2
col3

Need to get this select statement - col1,col2,col3 will come from c1 in Table1
select col1,col2,col3 from table2
where ..

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-18 : 13:45:28

clears as mud

Want to provide some sample data and expected results...in any case, I don't like where this is heading...can you give a brief description of WHY you are doing what you are doing?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dineshreddy
Starting Member

7 Posts

Posted - 2011-03-18 : 13:56:37

@X002548

Table1
UserID RoleID
A0 1
A0 2
A1 3
A1 1
A2 1

Table2
UserID UserNM
A0 bill
A1 tim
A2 john

Table3
RoleID RoleNM
1 admin
2 sysadmin
3 user
4 general
Result Query :

UserNM admin sysadmin user general
bill 1 1 0 0
tim 1 0 1 0
john 1 0 0 0

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 14:20:28

No need for Dynamic SQL

DECLARE @Table1 TABLE(UserID char(2),RoleId tinyint)
INSERT INTO @Table1
SELECT 'A0', 1 UNION ALL
SELECT 'A0',2 UNION ALL
SELECT 'A1',3 UNION ALL
SELECT 'A1',1 UNION ALL
SELECT 'A2',1

DECLARE @Table2 TABLE(UserID char(2),UserNm varchar(5))
INSERT INTO @Table2
SELECT 'A0','bill' UNION
SELECT 'A1','tim' UNION
SELECT 'A2','john'

DECLARE @Table3 TABLE(RoleId tinyint,RoleNm varchar(10))
INSERT INTO @Table3
SELECT 1,'admin' UNION
SELECT 2,'sysadmin' UNION
SELECT 3,'user' UNION
SELECT 4,'general'

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 14:29:16

[code]SELECT UserNM, ADMIN, SysAdmin, [USER], GENERAL
FROM (SELECT A.UserNM, C.RoleNM, B.RoleID
FROM Table2 A
INNER JOIN Table1 B ON A.UserID=B.UserID
LEFT JOIN Table3 C ON B.RoleID=C.RoleID) Z
PIVOT(COUNT(RoleID) FOR RoleNM IN (ADMIN, SysAdmin, [USER], GENERAL)) B[/code]edit: removed temp table symbols

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 14:38:14

I guess I should have added this part. But your solution is better
SELECT
t2.UserNm
,MAX(CASE WHEN t3.RoleNm = 'admin' THEN 1 ELSE 0 END) as [Admin]
,MAX(CASE WHEN t3.RoleNm = 'sysadmin' THEN 1 ELSE 0 END) as [sysAdmin]
,MAX(CASE WHEN t3.RoleNm = 'user' THEN 1 ELSE 0 END) as [user]
,MAX(CASE WHEN t3.RoleNm = 'general' THEN 1 ELSE 0 END) as [general]

FROM @table2 t2

INNER JOIN @table1 t1 ON t2.UserId = t1.UserID
INNER JOIN @table3 t3 ON t1.roleId = t3.RoleID

GROUP BY t2.UserNm

Jim

Everyday I learn something that somebody else already knew

dineshreddy
Starting Member

7 Posts

Posted - 2011-03-18 : 14:51:25

What if a new role is added in Table3 ? Do we have to manually change the query (or) Is there an alternative to handle this situation ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 14:53:45

You'll need some dynamic SQL to add newly pivoted columns. Take your pick of new style:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Or old:

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 15:59:55

quote:
Originally posted by jimf

I guess I should have added this part. But your solution is better

Well, mine works, but it doesn't really super work: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158385

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources