SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to convert Row data to columns


Query to convert Row data to columns

Author
Message
virgo
virgo
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 520
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
tertiusdp
tertiusdp
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 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...
tertiusdp
tertiusdp
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 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
virgo
virgo
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 520
Thanks a lot for that script.Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search