Solved

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

Posted on 2014-02-25
4
177 Views
Last Modified: 2014-03-04
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
0
Comment
Question by:bujjigadu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39886538
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
0
 

Author Comment

by:bujjigadu
ID: 39887193
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
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 355 total points
ID: 39887209
You can loop but it is not recommended. Pivot exists for exact this reason, to turn column values in different rows in different column in the same row.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39887252
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.....)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question