May 9, 2011 at 3:53 pm
Hi, I can't seem to wrap my head around adding an additional column to this query.
DECLARE @strValues varchar(8000)
SELECT @strValues = COALESCE(@strValues+',', '') + name
FROM (SELECT DISTINCT Name FROM Application) X
SELECT @strValues as Result
The query outputs a comma seperated list of all the applications in my environment.
There is another table called application_instance that has applicationid and instanceid columns.
I would like to list all the applications that are used by each instance of SQL Server. I think I have to join the first query to the second table so it can group by instance but I just can't seem to make it work because applicationid is not in the result of the first query.
Does anyone have a suggestion?
Thanks much,
Howard
May 10, 2011 at 7:04 am
Howard I think either of these two examples might get you where you want to go:
see if these get you closer to your goal:
SELECT
instz.applicationid,
instz.instanceid,
appz.Name
FROM application_instance instz
LEFT OUTER JOIN [Application] appz
ON instz.applicationid = appz.applicationid
--or if there is a lot of repeats in [Application]
SELECT
instz.applicationid,
instz.instanceid,
appz.Name
FROM application_instance instz
LEFT OUTER JOIN (SELECT
applicationid,
Name
FROM(SELECT
row_number() OVER(PARTITION BY applicationid,Name ORDER BY applicationid,Name) AS RW,
applicationid,
Name
FROM [Application]
)InnerQuery
WHERE RW = 1 ) appz
ON instz.applicationid = appz.applicationid
Lowell
May 10, 2011 at 10:19 am
Thanks for responding Lowell.
My goal is to have one row per instance that has a comma separated list of applications. To accomplish that I used to coalesce method in the top post but I'm not sure how to join the comma separated list and the instance number which comes from another table.
Howard
May 10, 2011 at 11:09 am
What about
Select Distinct
InstanceId
,Substring(Replace
(Replace((Select ','+Name
From Application
where ApplicationId=appinst.ApplicationId
FOR XML RAW),'<Row Data="',''),'"/>',''),2,4000) Data
from application_instance appinst
It may need a couple of tweaks, but should be pretty close.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
May 10, 2011 at 1:02 pm
Thanks Jason.
I gave the row a name 'test' and then it ran.
It adds a new row for each application.
instanceid application
417row test=",Application1
417row test=",Application2
418row test=",Application3
instead of
417 Application1, Application2
418 Application3
May 10, 2011 at 2:58 pm
PHXHoward (5/10/2011)
Thanks Jason.I gave the row a name 'test' and then it ran.
It adds a new row for each application.
instanceid application
417row test=",Application1
417row test=",Application2
418row test=",Application3
instead of
417 Application1, Application2
418 Application3
To add a label onto the problem...you're looking for an aggregate that concatenates grouped strings...MySQL has the built-in function GROUP_CONCAT for this type of work.
Here's a SQLCLR solution for SQL Server: http://groupconcat.codeplex.com
And here's a thread containing a T-SQL solution: http://www.sqlservercentral.com/Forums/FindPost1105840.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 10, 2011 at 4:16 pm
Thanks for the link! I always learn a lot. This SQL worked for me. It has a listing of Server, Instance, and associated applications.
declare @data Table
(
applicationid integer not null,
instanceid integer not null
)
insert into @data
select distinct ai.applicationid, ai.instanceid
from Application_Instance ai
select S.name, I.Name, r.concatenated
from
(select distinct instanceid from @data) as AI
CROSS APPLY
(SELECT ', ' + CONVERT(VARCHAR(500), A.name)
FROM @data AS D
join Application A
on D.applicationid = A.ApplicationID
WHERE
D.instanceid = AI.instanceid
ORDER BY
A.name ASC
FOR XML
PATH (''), TYPE
) AS GC (concatenated)
CROSS APPLY
(
-- Convert to VARCHAR from XML and remove leading delimiter
SELECT
STUFF(GC.concatenated.value('./text()[1]', 'VARCHAR(MAX)'), 1, 1, SPACE(0))
) AS R (concatenated)
join Instance i
on AI.instanceid = i.InstanceID
join Server S
on I.ServerID = S.ServerID
May 11, 2011 at 12:53 am
PHXHoward (5/10/2011)
Thanks Jason.I gave the row a name 'test' and then it ran.
It adds a new row for each application.
instanceid application
417row test=",Application1
417row test=",Application2
418row test=",Application3
instead of
417 Application1, Application2
418 Application3
Glad you found a solution, never quite sure about using CRL's as some DBA's can be a little funny about allowing them on their servers, especially if the source code is not available.
With my solution all you needed to do was to change the Replace, so that it read,
Select Distinct InstanceId
,Substring(Replace (Replace((Select ','+Name Test
From Application
where ApplicationId=appinst.ApplicationId
FOR XML RAW),'<Row Test="',''),'"/>',''),2,4000) Data
from application_instance appinst
_________________________________________________________________________
SSC Guide to Posting and Best Practices
May 11, 2011 at 6:07 am
Jason, your solution, while it may produce the correct result, will inevitably perform very poorly when run against a large set of data. I would offer one tweak to trade DISTINCT in for GROUP BY, like this:
Select InstanceId
,Substring(Replace (Replace((Select ','+Name Test
From Application
where ApplicationId=appinst.ApplicationId
FOR XML RAW),'<Row Test="',''),'"/>',''),2,4000) Data
from application_instance appinst
group by InstanceId
When done using a GROUP BY the sub-select in the SELECT-column-list will only be run once per unique InstanceId. In your query the sub-select is run for all InstanceId's, not just once per unique InstanceID, and then the DISTINCT is applied so it runs against a lot more data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2011 at 6:10 am
PS Be careful with using the XML RAW method as well...it escapes characters like ampersands and angle brackets to their XML-safe equivalents (namely &amp; , &lt; and &gt;) so if you do not know your data and the escaping is not acceptable you would want to use the XML PATH, TYPE solution (see my earlier link to Paul White's SSC post).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2011 at 9:49 am
Jason,
Your query still does not group the data into one row per instanceid.
it looks like this:
417 Application1
417 Application2
418 Application1
May 11, 2011 at 9:52 am
opc.three (5/11/2011)
Jason, your solution, while it may produce the correct result, will inevitably perform very poorly when run against a large set of data. I would offer one tweak to trade DISTINCT in for GROUP BY, like this:
Select InstanceId,Substring(Replace (Replace((Select ','+Name Test
From Application
where ApplicationId=appinst.ApplicationId
FOR XML RAW),'<Row Test="',''),'"/>',''),2,4000) Data
from application_instance appinst
group by InstanceId
When done using a GROUP BY the sub-select in the SELECT-column-list will only be run once per unique InstanceId. In your query the sub-select is run for all InstanceId's, not just once per unique InstanceID, and then the DISTINCT is applied so it runs against a lot more data.
Opc.three,
This query can't run because:
Column 'application_instance.ApplicationID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
May 11, 2011 at 10:00 am
PHXHoward (5/11/2011)
opc.three (5/11/2011)
Jason, your solution, while it may produce the correct result, will inevitably perform very poorly when run against a large set of data. I would offer one tweak to trade DISTINCT in for GROUP BY, like this:
Select InstanceId,Substring(Replace (Replace((Select ','+Name Test
From Application
where ApplicationId=appinst.ApplicationId
FOR XML RAW),'<Row Test="',''),'"/>',''),2,4000) Data
from application_instance appinst
group by InstanceId
When done using a GROUP BY the sub-select in the SELECT-column-list will only be run once per unique InstanceId. In your query the sub-select is run for all InstanceId's, not just once per unique InstanceID, and then the DISTINCT is applied so it runs against a lot more data.
Opc.three,
This query can't run because:
Column 'application_instance.ApplicationID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I don't have your environment or test data but I tested the technique in my environment and it works fine as described...just not as written apparently. I must have translated my query to your table and column names incorrectly. Point is...use the CLR or Paul White's T-SQL technique...they do not suffer from the escaping issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2011 at 10:31 am
Here's a working demo with the GROUP BY that shows the escape issue:
SET NOCOUNT ON ;
GO
IF OBJECT_ID(N'tempdb..#Data') > 0
DROP TABLE #Data ;
GO
CREATE TABLE #Data
(
id INTEGER NOT NULL,
data VARCHAR(100) NOT NULL
) ;
GO
-- Sample data
INSERT #Data
(id, data)
VALUES (1, '1 & 2'),
(1, 'a'),
(2, '1 < 2'),
(2, 'b'),
(3, 'hello!'),
(3, '2 > 1') ;
GO
-- show XML RAW technique
SELECT d.id,
SUBSTRING(REPLACE(REPLACE((
SELECT ',' + data AS Test
FROM #Data
WHERE id = d.id
FOR
XML RAW
), '<Row Test="', ''), '"/>', ''), 2, 4000) Data
FROM #Data d
GROUP BY d.id ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 12, 2011 at 2:05 am
Thanks for the feed back opc, especially on the Distinct vs Group By modification and the tips on XML escaping characters, based on the opening post I would have thought that the Application name wouldnt have escapable characters, though its always possible.
The CTE is probably the rout I'd go down in the future, and try and stay clear of the CLR.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply