Returning single row from multiple rows
-
Hello _
I face this case.
i have this tables
table1: users (userid , username .....)
table2: roles (roleid,rolename....)
table3: divisions (divisionid,divisionname .....)
table4: users_roles (userid,roleid,divisionid .....)
i want to select the users with there roles and divisions in single row ,
the out put will be like that
username divisionname divisionrole divisionname divisionrole divisionname divisionrole
peter division1 admin division2 user division3 helper
can any one help please
thanks
-
This 'may' work, but it is much more helpful when you supply us with sample data.
The -- PRINT @sql statements should help you see what is happening...
CREATE TABLE #Users( UserID integer,
UserName varchar(10))
INSERT INTO #Users VALUES( 1, 'Peter')
-------------------------------------------------------------
CREATE TABLE #Roles( RoleID integer,
RoleName varchar(10))
INSERT INTO #Roles VALUES( 1, 'Unknown')
-------------------------------------------------------------
CREATE TABLE #Divisions( DivisionID integer,
DivisionName varchar(10),
DivisionRole varchar(10))
INSERT INTO #Divisions VALUES( 1, 'Division1', 'Admin')
INSERT INTO #Divisions VALUES( 2, 'Division2', 'User')
INSERT INTO #Divisions VALUES( 3, 'Division3', 'Helper')
-------------------------------------------------------------
CREATE TABLE #Users_Roles( UserID integer,
RoleID integer,
DivisionID integer)
INSERT INTO #Users_Roles VALUES( 1, 1, 1)
INSERT INTO #Users_Roles VALUES( 1, 1, 2)
INSERT INTO #Users_Roles VALUES( 1, 1, 3)
-------------------------------------------------------------
CREATE TABLE #OutPut( UserID integer,
UserName varchar(10))
INSERT INTO #OutPut( UserID, UserName) SELECT DISTINCT UserID, UserName FROM #Users
-------------------------------------------------------------
DECLARE @sql varchar(2000),
@Counter integer,
@CurrentDivisionID integer,
@MaxDivisionID integer,
@DivisionName varchar(25),
@DivisionRole varchar(25)
SELECT @CurrentDivisionID = (SELECT MIN( DivisionID) FROM #Divisions)
SELECT @MaxDivisionID = (SELECT MAX( DivisionID) FROM #Divisions)
SELECT @Counter = 1
WHILE @CurrentDivisionID <= @MaxDivisionID
BEGIN
SELECT @DivisionName = 'DivisionName' + CONVERT( char, @Counter)
SELECT @DivisionRole = 'DivisionRole' + CONVERT( char, @Counter)
SELECT @sql = 'ALTER TABLE #OutPut ADD ' + RTRIM( LTRIM( @DivisionName)) + ' varchar(10) NULL'
EXEC( @sql)
--PRINT @sql
SELECT @sql = 'ALTER TABLE #OutPut ADD ' + RTRIM( LTRIM( @DivisionRole)) + ' varchar(10) NULL'
EXEC( @sql)
--PRINT @sql
SELECT @sql = 'UPDATE #OutPut SET ' + CHAR(10) +
RTRIM( LTRIM( @DivisionName)) + ' = DivisionName, '+ CHAR(10) +
RTRIM( LTRIM( @DivisionRole)) + ' = DivisionRole ' + CHAR(10) +
'FROM #Divisions WHERE DivisionID = ' + CONVERT( char(5), @CurrentDivisionID)
EXEC( @sql)
--PRINT @sql
SELECT @CurrentDivisionID = (SELECT MIN( DivisionID) FROM #Divisions WHERE DivisionID > @CurrentDivisionID)
SELECT @Counter = @Counter + 1
END
SELECT * FROM #OutPut
DROP TABLE #Users
DROP TABLE #Roles
DROP TABLE #Divisions
DROP TABLE #Users_Roles
DROP TABLE #OutPut
I wasn't born stupid - I had to study.
-
Using the table structures and data provided by Farrell above, you can also do this:
SELECT USERS.USERNAME,
MAX(CASE WHEN DIVISIONS.DIVISIONID = 1 THEN DIVISIONNAME ELSE NULL END) DIVISION_1,
MAX(CASE WHEN DIVISIONS.DIVISIONID = 1 THEN DIVISIONROLE ELSE NULL END) DIVISION_ROLE_1,
MAX(CASE WHEN DIVISIONS.DIVISIONID = 2 THEN DIVISIONNAME ELSE NULL END) DIVISION_2,
MAX(CASE WHEN DIVISIONS.DIVISIONID = 2 THEN DIVISIONROLE ELSE NULL END) DIVISION_ROLE_2,
MAX(CASE WHEN DIVISIONS.DIVISIONID = 3 THEN DIVISIONNAME ELSE NULL END) DIVISION_3,
MAX(CASE WHEN DIVISIONS.DIVISIONID = 3 THEN DIVISIONROLE ELSE NULL END) DIVISION_ROLE_3
FROM USERS, USERS_ROLES, DIVISIONS
WHERE USERS.USERID = USERS_ROLES.USERID
AND USERS_ROLES.DIVISIONID = DIVISIONS.DIVISIONID
GROUP BY USERNAME
--output
USERNAME DIVISION_1 DIVISION_ROLE_1 DIVISION_2 DIVISION_ROLE_2 DIVISION_3 DIVISION_ROLE_3
---------- ---------- --------------- ---------- --------------- ---------- ---------------
Peter Division1 Admin Division2 User Division3 Helper
(1 row(s) affected)
Do note that this approach will work if you have a finite maximum number of rows that you want to convert to columns i.e. first identify the maximum number of divisions that a user can belong to and then then based on it, change the query so that new columns will be made available after the row--> columns migration. If the number is not known before-hand and can change, then you would have to do it either via a UDF.
Hth
-
I agree with rsharma. I was not sure if there was a finite set, hence the clunky dynamic sql approach. If you have a finite set, I would follow that suggestion and a UDF would be good.
I wasn't born stupid - I had to study.
-
This is a classic "pivot table" pattern.
There are many ways to solve this, and the replies so far are good solutions, but I think it's important to know the name for this very common pattern.
-
Thanks Peter. That is exactly correct and it has been a real booger for me with the versions of SQL Server. I hear tell Yukon or SQL Server 2005, (or whatever it is called now...) should have that built into it. Do you know if this is the case?
I wasn't born stupid - I had to study.
-
Here's a link that covers the SQL Server 2005 pivot capabilities: devx
-
Excellent! I have recently gotten a copy to install at home, (I am currently using the free MySQL - so this will be nice to have a real version).
I will play with this function as it has been such a serious pain up to now.
I wasn't born stupid - I had to study.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply