Avatar of bujjigadu
bujjigadu 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
Microsoft SQL Server

Avatar of undefined
Last Comment
Zberteoc

8/22/2022 - Mon
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
ASKER
bujjigadu

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.....)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy