Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to convert Row data to columns Expand / Collapse
Author
Message
Posted Thursday, July 31, 2008 11:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:23 AM
Points: 53, Visits: 287
Hi All,

I have a table containing Applicationid,applicationname,source where an unique application id has
different Applicationname and Source. The applicationname with source 'AR' will be considered as the original application name while corresponding other application names are considered as the aliases for that application name.

belwo is the table structure i have

Table I have

AppID ApplicationName Source
22 Acrobat V5.0 AR
22 Adobe Acrobat WM
22 Acrobat Distiller WMTSFRM
22 Acrobat Reader WMTSFRM
59 ALIS - Advanced AR
59 Alis FRM
59 Comm – ALIS SFRM

The sql out put format format I need is

ApplicationName Aliase1 Aliase2 Aliase3
Acrobat V5.0 Adobe Acrobat Acrobat Distiller Acrobat Reader
ALIS - Advanced Alis Comm – ALIS


Can anyone please help in generating the sql query to get the output in the above mentioned format.
sql server version :2005 (hope there is some way tru pivot)

Thanks in advance
Post #544852
Posted Friday, August 1, 2008 2:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:54 AM
Points: 359, Visits: 244
This is how to do it if you know how many aliases you are going to have:

;WITH CTE_PIVOT
AS
(
SELECT
Applicationid,
CASE SOURCE WHEN 'AR' THEN applicationname ELSE NULL END AS applicationname,
CASE ROW_NUMBER() OVER ( PARTITION BY Applicationid ORDER BY CASE SOURCE WHEN 'AR' THEN 0 ELSE 1 END ) WHEN 2 THEN applicationname ELSE NULL END AS ALIAS1,
CASE ROW_NUMBER() OVER ( PARTITION BY Applicationid ORDER BY CASE SOURCE WHEN 'AR' THEN 0 ELSE 1 END ) WHEN 3 THEN applicationname ELSE NULL END AS ALIAS2,
CASE ROW_NUMBER() OVER ( PARTITION BY Applicationid ORDER BY CASE SOURCE WHEN 'AR' THEN 0 ELSE 1 END ) WHEN 4 THEN applicationname ELSE NULL END AS ALIAS3
FROM #apps
)
SELECT
MAX(applicationname) AS applicationname,
MAX(CTE_PIVOT.ALIAS1) AS ALIAS1,
MAX(CTE_PIVOT.ALIAS2) AS ALIAS2,
MAX(CTE_PIVOT.ALIAS3) AS ALIAS3
FROM CTE_PIVOT
GROUP BY Applicationid


The ROW_NUMBER() function returns a row number (makes sense somehow!!) for every row that is returned. I am using partition by to reset the row numbering after every application id. I am also ordering it by a case statement to make sure the application name is the first row returned. Then I just do a case statement that puts the application names in the correct columns, and then I do a group by in the final query to roll the rows up.

Dynamic query to follow later...
Post #544913
Posted Friday, August 1, 2008 3:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:54 AM
Points: 359, Visits: 244
This is the dynamic one. I prefer not to do dynamic SQL in my projects but sometimes it is unavoidable. Maybe see if you can do something on the client side rather than doing dynamic SQL.

In any case, here follows the query:


DECLARE @COLUMNS INT
DECLARE @SQLSTR NVARCHAR(MAX)
DECLARE @I INT

SELECT @I = 2

--GET THE NUMBER OF ALIASES WE WILL HAVE TO ITERATE OVER
;WITH CTE_APPS
AS
(
SELECT
COUNT(*) AS COUNTED
FROM #apps
GROUP BY Applicationid
)
SELECT @COLUMNS = MAX(COUNTED) FROM CTE_APPS

SELECT @SQLSTR = '
;WITH CTE_PIVOT
AS
(
SELECT
Applicationid,
CASE SOURCE WHEN ''AR'' THEN applicationname ELSE NULL END AS applicationname,
'

--1ST LOOP TO DO THE PIVOT
WHILE @I <= @COLUMNS
BEGIN
SELECT @SQLSTR = @SQLSTR + 'CASE ROW_NUMBER() OVER ( PARTITION BY Applicationid ORDER BY CASE SOURCE WHEN ''AR'' THEN 0 ELSE 1 END ) WHEN ' + CONVERT(VARCHAR,@I) + ' THEN applicationname ELSE NULL END AS ALIAS' + CONVERT(VARCHAR,@I - 1 )
IF @I < @COLUMNS SELECT @SQLSTR = @SQLSTR + ',
'

SELECT @I = @I + 1
END

SELECT @SQLSTR = @SQLSTR + '
FROM #apps
)
SELECT
MAX(applicationname) AS applicationname,
'

--LOOP TO CREATE THE COLUMN LIST
SELECT @I = 1
WHILE @I < @COLUMNS
BEGIN
SELECT @SQLSTR = @SQLSTR + 'MAX(CTE_PIVOT.ALIAS' + CONVERT(VARCHAR,@I) + ') AS ALIAS' + CONVERT(VARCHAR,@I)
IF @I < @COLUMNS - 1 SELECT @SQLSTR = @SQLSTR + ',
'

SELECT @I = @I + 1
END

SELECT @SQLSTR = @SQLSTR + '
FROM CTE_PIVOT
GROUP BY Applicationid '

PRINT @SQLSTR

EXEC SP_EXECUTESQL @SQLSTR
Post #544926
Posted Sunday, August 3, 2008 11:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:23 AM
Points: 53, Visits: 287
Thanks a lot for that script.:)
Post #545883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse