asked on

How to display multiple column values as rows in my stored procedure

I have a stored procedure that display column values like this

ID Entity UnitID UnitName RoleID

1 Test 1 UnitTest 1
1 Test 1 UnitTest 2
1 Test 1 UnitTest 4

As you can see all my column values are same except RoleId changes What I want to see as

ID Entity UnitID UnitName RoleID RoleID1 RoleID2

1 Test 1 UnitTest 1 2 4

Instead of displaying three times I want to have one row and create some temporary columns (RoleID1 and RoleID2) to store the values and sent back the results

I'm attaching my stored procedure here and I'm using microsoft sql server 2012
StoredProce-EE.txt

Zberteoc

This is pivoting but in order to be able to do this you will have to know the maximum unittests you have and then use that to build that many columns.

To pivot:

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

or:

http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

bujjigadu

ASKER

For some reason the pivot table is not giving me the results I'm looking for. Is there anyway I can loop through current results and build a temp table

like loop through this result set

ID Entity UnitID UnitName RoleID

1 Test 1 UnitTest 1
1 Test 1 UnitTest 2
1 Test 1 UnitTest 4

and build a temp table with this result set

ID Entity UnitID UnitName RoleID RoleID1 RoleID2

1 Test 1 UnitTest 1 2 4

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zberteoc

Try this:

ALTER PROCEDURE [dbo].[TestingUnits_getAllUserPerms] 
	-- Add the parameters for the stored procedure here
	@UserName varchar(80), 
	@RoleID int,
	@RoundingUserName varchar(80),
	@RoundingUserRoleID int,
	@RoundingUserRoleID1 int,
	@RoundingUserRoleID2 int,
	@EntityID int 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT * INTO #tmp
FROM
(
	    SELECT nu.EntityID,
		e.[EntityName], 
		nu.UnitID, 
		UnitName,
		nu.Active,
		r.roleid
		FROM testUnits nu
		inner join [dbo].[Entity] e on e.EntityID = nu.EntityID
		,UserRole r
		WHERE 
			(nu.EntityID = @EntityID)
			AND
			(nu.UnitID  IN (SELECT UnitID FROM UserAuthorizedUnitsInRole(@RoundingUserName, @RoundingUserRoleID)))
			AND
			(nu.UnitID  IN (SELECT UnitID FROM UserAuthorizedUnitsInRole(@UserName, @RoleID)) AND (@UserName IS NOT NULL) AND (@UserName <> ''))
			AND
			r.roleid not in (6,7)
UNION

		SELECT nu.EntityID,
		e.[EntityName], 
		nu.UnitID, 
		UnitName,
		nu.Active,
		r.roleid
		FROM testUnits nu
		inner join [dbo].[Entity] e on e.EntityID = nu.EntityID
		,UserRole r
		WHERE 
			(nu.EntityID = @EntityID)
			AND
			(nu.UnitID IN (SELECT UnitID FROM UserAuthorizedUnitsInRole(@RoundingUserName, @RoundingUserRoleID1)))
			AND
			(nu.UnitID IN (SELECT UnitID FROM UserAuthorizedUnitsInRole(@UserName, @RoleID)) AND (@UserName IS NOT NULL) AND (@UserName <> ''))
	        AND
			r.roleid not in (6,7)
UNION

		SELECT nu.EntityID,
		e.[EntityName], 
		nu.UnitID, 
		UnitName,
		nu.Active,
		r.roleid
		FROM testUnits nu
		inner join [dbo].[Entity] e on e.EntityID = nu.EntityID
		,UserRole r
		WHERE 
			(nu.EntityID = @EntityID)
			AND
			(nu.UnitID IN (SELECT UnitID FROM UserAuthorizedUnitsInRole(@RoundingUserName, @RoundingUserRoleID2)))
			AND
			(nu.UnitID IN (SELECT UnitID FROM UserAuthorizedUnitsInRole(@UserName, @RoleID)) AND (@UserName IS NOT NULL) AND (@UserName <> ''))
			AND
			r.roleid not in (6,7)

UNION

		SELECT nu.EntityID,
		e.[EntityName], 
		nu.UnitID, 
		UnitName,
		nu.Active,
		r.roleid
		FROM testUnits nu
		inner join [dbo].[Entity] e on e.EntityID = nu.EntityID
		,UserRole r
		WHERE 
			(nu.EntityID = @EntityID)
			AND
			r.roleid not in (6,7)
) q
	ORDER BY UnitName

SELECT *
FROM (
    SELECT 
        EntityID,
		EntityName, 
        UnitID,
		Active,
		UnitName,
		RoleID
    FROM 
		#tmp
) as s
PIVOT
(
    MIN(UnitName)
    FOR RoleID IN (1,2,3,4,5,6,7,8,9,10)
)AS pivot
END

Open in new window

If you have more than 10 RoleID values you will have to increase to maximum:

FOR RoleID IN (1,2,3,4,5,6,7,8,9,10.....)