July 31, 2008 at 11:27 pm
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
22Acrobat V5.0 AR
22Adobe Acrobat WM
22Acrobat Distiller WMTSFRM
22Acrobat Reader WMTSFRM
59ALIS - Advanced AR
59Alis FRM
59Comm – 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
August 1, 2008 at 2:50 am
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...
August 1, 2008 at 3:08 am
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
August 3, 2008 at 11:23 pm
Thanks a lot for that script.:)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply