InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 10:10:57 GMT20http://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxSure I do and sure I will. Not here, Orlando. Let's go to another thread if you want to continue.Mon, 06 May 2013 04:53:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]Jeff Moden (5/5/2013)[/b][hr][quote][b]opc.three (5/5/2013)[/b][hr]I think Wayne already called out that the comment was misplaced.[u][url]http://www.sqlservercentral.com/Forums/FindPost1046450.aspx[/url][/u]I thought you were taking things in a different direction.[/quote]Yeah, but I need to call it out.[/quote]Why? What's your point?[quote]On the other thing, I don't want to ruin Wayne's fine discussion with discussions on visceral fear. :-)[/quote]No Jeff, that's not how it works. You do not get to make petty jabs and then try taking the high ground while using Wayne's (fine) article as a shield.Regarding "visceral fear", can you really blame me? Have you seen this thread?[u][url=http://www.sqlservercentral.com/Forums/Topic1445014-391-1.aspx]http://www.sqlservercentral.com/Forums/Topic1445014-391-1.aspx[/url][/u]S******y issues aside, do you think if more people knew that every so often when you call xp_cmdshell that it might force you to restart the entire SQL Server service that more people might sart avoiding it? :Whistling:Sun, 05 May 2013 18:51:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]opc.three (5/5/2013)[/b][hr]I think Wayne already called out that the comment was misplaced.[u][url]http://www.sqlservercentral.com/Forums/FindPost1046450.aspx[/url][/u]I thought you were taking things in a different direction.[/quote]Yeah, but I need to call it out.On the other thing, I don't want to ruin Wayne's fine discussion with discussions on visceral fear. :-)Sun, 05 May 2013 17:10:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxI think Wayne already called out that the comment was misplaced.[u][url]http://www.sqlservercentral.com/Forums/FindPost1046450.aspx[/url][/u]I thought you were taking things in a different direction.Sun, 05 May 2013 10:37:55 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]opc.three (5/4/2013)[/b][hr][quote]...when this is so easily done without it and it works in a stored procedure or a function without having to somehow get to the command line to do it?[/quote]How would you do it in T-SQL?[/quote]If you're just outputing a result set (which is the object of the article... not exporting data), then just like the article does with the [text] kicker. I'm not sure why anyone would even bring up PowerShell in such a situation.Sun, 05 May 2013 09:44:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote]...when this is so easily done without it and it works in a stored procedure or a function without having to somehow get to the command line to do it?[/quote]How would you do it in T-SQL?Sat, 04 May 2013 21:54:52 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]glock 71629 (1/12/2011)[/b][hr]This is exactly the situation where I use PowerShell frequently. I am able to export data from whatever complex query. Idea is something like this:[code]Invoke-Sqlcmd -ServerInstance myServer -Database MyDb -Query "SELECT * FROM SomeTable" | Export-Csv ./result.csv -NoTypeInformation[/code]In -Query can be any T-SQL code and results are exported natively to CSV file. I work as ConfigMgr admin and this is the way how you can really easily receive your data.David[/quote]I know this post is more than 2 years old but curiosity finally got the best of me. "Everyone" keeps recommending that PowerShell be used for these types of things but not once have I seen them identify how you could use PowerShell in a stored procedure. ;-) Also, why even bother with PowerShell when this is so easily done without it and it works in a stored procedure or a function without having to somehow get to the command line to do it?Yes, if you're trying to create a CSV file, the PowerShell solution works just fine but why would you use it instead of T-SQL if the target was also T-SQL or just a result set to an app?Sat, 04 May 2013 20:31:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxNice article!Tue, 25 Dec 2012 08:13:54 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxNice article.Mon, 24 Dec 2012 12:30:00 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]cs_troyk (11/9/2012)[/b][hr][quote][b]opc.three (11/8/2012)[/b][hr][quote][b]cs_troyk (11/8/2012)[/b][hr][quote][b]opc.three (11/8/2012)[/b][hr][quote][b]cs_troyk (10/29/2012)[/b][hr]Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.-TroyK[/quote]MySQL's GROUP_CONCAT() function implements a semantically different piece of functionality than what Wayne has demonstrated in the article. Notice that the query in the article is not an aggregate, i.e. no GROUP BY clause.[/quote]Take a look at the CTE from the article, and consider the effect of the "DISTINCT" keyword.Here's the MySQL solution to the comma-separated list problem:SELECT AccountNumber, GROUP_CONCAT(Value ORDER BY Value) AS 'CommaList'FROM TGROUP BY AccountNumberORDER BY AccountNumber;It produces the same output as the T-SQL solution.-TroyK[/quote]Not sure what you're getting at when you talk about DISTINCT, whether you mean in the outer query or the correlated subquery, but if the subquery you would need to provide DISTINCT when using GROUP_CONCAT() as well, if duplicate values were a concern:[code="sql"SELECT AccountNumber, GROUP_CONCAT([b]DISTINCT[/b] Value ORDER BY Value) AS 'CommaList'FROM TGROUP BY AccountNumberORDER BY AccountNumber;[/code]If the outer query then yes, I agree, an aggregate may be a better option. I am not arguing about the likeness of the results however. All I am pointing out is that there is a difference in implementation, semantical maybe, but there is a penalty for using GROUP BY when it is not needed when compared to using a correlated subquery as Wayne presents it. If GROUP_CONCAT() existed on SQL Server the XML technique from the article would almost certainly outperform it so while it may seem like a fair comparison from a query results point of view it's really apples and oranges under the hood.Now, from a syntax point of view, when the outer query must be distinct, I like the GROUP_CONCAT() method which is why I wrote [u][url=http://groupconcat.codeplex.com/]GROUP_CONCAT() for SQL Server[/url][/u] by leveraging the SQLCLR but it is not the best choice for all scenarios.[/quote]My point is that the solution presented in the article is the equivalent to a solution using GROUP BY, so saying that there's no GROUP BY clause is only accurate when talking about the syntax. The query could be rewritten like this, and it's the same query from the optimizer's perspective:[code="sql"]SELECT AccountNumber, CommaList = STUFF(( SELECT ',' + Value FROM #TestData WHERE AccountNumber = t.AccountNumber ORDER BY Value FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'') FROM #TestData t GROUP BY AccountNumber ORDER BY AccountNumber;[/code]This produces the same query plan as the CTE version, and has the same IO profile, so it is incorrect to assert some penalty for using GROUP BY. I'm not sure how you arrive at the conclusion that the XML version would outperform GROUP_CONCAT if the latter were implemented in MS's product. Is this based on your results from implementing it using CLR?-TroyK[/quote]Yes they are. I am now on the same page as far as which DISTINCT you were referring too. If the dataset you're aggregating on is [i]not[/i] unique and you do not [i]need[/i] to apply DISTINCT then SQL will not need to sort or de-duplicate the key column to aggregate the results, whereas the GROUP BY always will (unless the optimizer can ignore it due to the presence of a unique index). That is where the penalty I am referring too is introduced.Here is the equivalent query using my SQLCLR:[code="sql"]SELECT AccountNumber, dbo.GROUP_CONCAT(Value) AS CommaListFROM #TestData tGROUP BY AccountNumberORDER BY AccountNumber;[/code]In my testing (test scripts are included in the CodePlex download) the SQLCLR object is on par with the XML TYPE method shown in the article in terms of performance. In some test cases the XML performs best, in others the SQL CLR performs best. The +- is roughly 10% in either direction so I am comfortable saying they are "comparable" for general use.Fri, 09 Nov 2012 13:45:41 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]opc.three (11/8/2012)[/b][hr][quote][b]cs_troyk (11/8/2012)[/b][hr][quote][b]opc.three (11/8/2012)[/b][hr][quote][b]cs_troyk (10/29/2012)[/b][hr]Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.-TroyK[/quote]MySQL's GROUP_CONCAT() function implements a semantically different piece of functionality than what Wayne has demonstrated in the article. Notice that the query in the article is not an aggregate, i.e. no GROUP BY clause.[/quote]Take a look at the CTE from the article, and consider the effect of the "DISTINCT" keyword.Here's the MySQL solution to the comma-separated list problem:SELECT AccountNumber, GROUP_CONCAT(Value ORDER BY Value) AS 'CommaList'FROM TGROUP BY AccountNumberORDER BY AccountNumber;It produces the same output as the T-SQL solution.-TroyK[/quote]Not sure what you're getting at when you talk about DISTINCT, whether you mean in the outer query or the correlated subquery, but if the subquery you would need to provide DISTINCT when using GROUP_CONCAT() as well, if duplicate values were a concern:[code="sql"SELECT AccountNumber, GROUP_CONCAT([b]DISTINCT[/b] Value ORDER BY Value) AS 'CommaList'FROM TGROUP BY AccountNumberORDER BY AccountNumber;[/code]If the outer query then yes, I agree, an aggregate may be a better option. I am not arguing about the likeness of the results however. All I am pointing out is that there is a difference in implementation, semantical maybe, but there is a penalty for using GROUP BY when it is not needed when compared to using a correlated subquery as Wayne presents it. If GROUP_CONCAT() existed on SQL Server the XML technique from the article would almost certainly outperform it so while it may seem like a fair comparison from a query results point of view it's really apples and oranges under the hood.Now, from a syntax point of view, when the outer query must be distinct, I like the GROUP_CONCAT() method which is why I wrote [u][url=http://groupconcat.codeplex.com/]GROUP_CONCAT() for SQL Server[/url][/u] by leveraging the SQLCLR but it is not the best choice for all scenarios.[/quote]My point is that the solution presented in the article is the equivalent to a solution using GROUP BY, so saying that there's no GROUP BY clause is only accurate when talking about the syntax. The query could be rewritten like this, and it's the same query from the optimizer's perspective:[code="sql"]SELECT AccountNumber, CommaList = STUFF(( SELECT ',' + Value FROM #TestData WHERE AccountNumber = t.AccountNumber ORDER BY Value FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'') FROM #TestData t GROUP BY AccountNumber ORDER BY AccountNumber;[/code]This produces the same query plan as the CTE version, and has the same IO profile, so it is incorrect to assert some penalty for using GROUP BY. I'm not sure how you arrive at the conclusion that the XML version would outperform GROUP_CONCAT if the latter were implemented in MS's product. Is this based on your results from implementing it using CLR?-TroyKFri, 09 Nov 2012 12:41:59 GMTcs_troykhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]cs_troyk (11/8/2012)[/b][hr][quote][b]opc.three (11/8/2012)[/b][hr][quote][b]cs_troyk (10/29/2012)[/b][hr]Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.-TroyK[/quote]MySQL's GROUP_CONCAT() function implements a semantically different piece of functionality than what Wayne has demonstrated in the article. Notice that the query in the article is not an aggregate, i.e. no GROUP BY clause.[/quote]Take a look at the CTE from the article, and consider the effect of the "DISTINCT" keyword.Here's the MySQL solution to the comma-separated list problem:SELECT AccountNumber, GROUP_CONCAT(Value ORDER BY Value) AS 'CommaList'FROM TGROUP BY AccountNumberORDER BY AccountNumber;It produces the same output as the T-SQL solution.-TroyK[/quote]Not sure what you're getting at when you talk about DISTINCT, whether you mean in the outer query or the correlated subquery, but if the subquery you would need to provide DISTINCT when using GROUP_CONCAT() as well, if duplicate values were a concern:SELECT AccountNumber, GROUP_CONCAT([b]DISTINCT[/b] Value ORDER BY Value) AS 'CommaList'FROM TGROUP BY AccountNumberORDER BY AccountNumber;If the outer query then yes, I agree, an aggregate may be a better option. I am not arguing about the likeness of the results however. All I am pointing out is that there is a difference in implementation, semantical maybe, but there is a penalty for using GROUP BY when it is not needed when compared to using a correlated subquery as Wayne presents it. If GROUP_CONCAT() existed on SQL Server the XML technique from the article would almost certainly outperform it so while it may seem like a fair comparison from a query results point of view it's really apples and oranges under the hood.Now, from a syntax point of view, when the outer query must be distinct, I like the GROUP_CONCAT() method which is why I wrote [u][url=http://groupconcat.codeplex.com/]GROUP_CONCAT() for SQL Server[/url][/u] by leveraging the SQLCLR but it is not the best choice for all scenarios.Thu, 08 Nov 2012 23:33:00 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]opc.three (11/8/2012)[/b][hr][quote][b]cs_troyk (10/29/2012)[/b][hr]Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.-TroyK[/quote]MySQL's GROUP_CONCAT() function implements a semantically different piece of functionality than what Wayne has demonstrated in the article. Notice that the query in the article is not an aggregate, i.e. no GROUP BY clause.[/quote]Take a look at the CTE from the article, and consider the effect of the "DISTINCT" keyword.Here's the MySQL solution to the comma-separated list problem:SELECT AccountNumber, GROUP_CONCAT(Value ORDER BY Value) AS 'CommaList'FROM TGROUP BY AccountNumberORDER BY AccountNumber;It produces the same output as the T-SQL solution.-TroyKThu, 08 Nov 2012 23:18:44 GMTcs_troykhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]cs_troyk (10/29/2012)[/b][hr]Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.-TroyK[/quote]MySQL's GROUP_CONCAT() function implements a semantically different piece of functionality than what Wayne has demonstrated in the article. Notice that the query in the article is not an aggregate, i.e. no GROUP BY clause.Thu, 08 Nov 2012 15:32:36 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxJust a note of caution - if you plan to string together integers with a comma delimiter and subsquently dump the data into Excel, Excel will try to do funny things with the string. I just ran into this today. I changed the delimiter to a pipe (|) and all is well.Tue, 30 Oct 2012 19:05:51 GMTPossinatorhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxSo, what you are looking for is:Col1, comma-separated list of Column 2 (in order)Col1 should only be there once.Col2 is an integer, and should be sorted by it's value.Is this correct?So, this is what you're looking for:[code="sql"]DECLARE @table TABLE ( Col1 INT, Col2 INT);INSERT INTO @table (Col1, Col2)SELECT 1, 1 UNION ALLSELECT 1, 2 UNION ALLSELECT 1, 5 UNION ALLSELECT 1, 10 UNION ALLSELECT 3, 1 UNION ALLSELECT 3, 5 UNION ALLSELECT 3, 10 UNION ALLSELECT 3, 15;WITH cte AS (SELECT DISTINCT Col1FROM @table)SELECT Col1, CS = STUFF((SELECT ',' + CONVERT(VARCHAR(10), Col2) FROM @table t1 WHERE t1.col1 = cte.Col1 ORDER BY Col2 FOR XML PATH(''), TYPE).value('.','varchar(max)'), 1, 1, '')FROM cteORDER BY Col1;[/code]Tue, 30 Oct 2012 12:36:46 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx^^^ or indeed any sense at all? ;-)Tue, 30 Oct 2012 11:14:38 GMTchris.westgatehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxSorry. Left out a couple of critical bits of information. I'm selecting two columns and ordering by one of them - but trying to get the CS string for the second column, against the first column... both columns are integers, and the second column has repeated values but I need the distinct list... and of course if distinct is used, the exact expression must appear in the order by list.However, because I've got to cast it as a varchar to concatenate the comma, I can only order by the cast as varchar - which doesn't give the correct integer sorting......does that make more sense now?Tue, 30 Oct 2012 11:12:18 GMTchris.westgatehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]chris.westgate (10/30/2012)[/b][hr]Great solution Wayne, thank you...But for me, here's a tiny spanner in the works.The column that I need to produce a delimited list from is an int column - and I need the list ordered by integer. However, trying to concatenate ',' with an int of course results in a conversion error trying to convert ',' to an int... BUT, if I cast my int value as a varchar, of course, it then produces a list that runs 1,10,11, 12, 2, 20... etc...Any ideas please?CheersChris[/quote]Hi Chris,In the article, I have an "ORDER BY Value" to control the ordering (just prior to the FOR XML). Just use your integer column there. As you (and Jeff) point out, just convert your int to a varchar to create the delimited list.Tue, 30 Oct 2012 08:58:25 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]chris.westgate (10/30/2012)[/b][hr]Great solution Wayne, thank you...But for me, here's a tiny spanner in the works.The column that I need to produce a delimited list from is an int column - and I need the list ordered by integer. However, trying to concatenate ',' with an int of course results in a conversion error trying to convert ',' to an int... BUT, if I cast my int value as a varchar, of course, it then produces a list that runs 1,10,11, 12, 2, 20... etc...Any ideas please?CheersChris[/quote]You've basically just answered you own question. The only way to concatenate INTs will be to convert them. Just add an ORDER BY to you XML based SELECT and Bob's your uncle.Tue, 30 Oct 2012 08:51:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxGreat solution Wayne, thank you...But for me, here's a tiny spanner in the works.The column that I need to produce a delimited list from is an int column - and I need the list ordered by integer. However, trying to concatenate ',' with an int of course results in a conversion error trying to convert ',' to an int... BUT, if I cast my int value as a varchar, of course, it then produces a list that runs 1,10,11, 12, 2, 20... etc...Any ideas please?CheersChrisTue, 30 Oct 2012 08:37:25 GMTchris.westgatehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxNot that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. It's something to be aware of in case you ever need to migrate from a MySQL db.-TroyKMon, 29 Oct 2012 20:12:22 GMTcs_troykhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxCOTS... say no more. I feel your pain.Fri, 26 Oct 2012 15:06:52 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxYeah well I guess that is more a function of it using views on top of a COTS application database based on two primary tables Component and Relationship. The system is very flexible because you can create any type of component and subsequent relationships between them that you want but it does have a performance cost due to so many joins just to include component properties for instance. The query I am referring to is based on a view that already contains such other included properties and relationships.The system is for capturing enterprise architecture components and relationships, so luckily the number of objects is relatively small.Fri, 26 Oct 2012 14:24:48 GMTed.wiebehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxThat's great Ed!... but 1 second is kinda slow for this method on 10,000 rowsFri, 26 Oct 2012 13:09:32 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxThis is great! I have been using the Coalesce function in a udf for this purpose, but this method is faster. I didn't use it as a correlated subquery, but I did replace the internals of my udf with the "For XML PATH" and it took a 3 second query for 10,000 records down to 1 second.Thank you!Fri, 26 Oct 2012 09:12:15 GMTed.wiebehttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxSorry: didn't spot how old this was....Fri, 26 Oct 2012 04:22:00 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[url=http://blogs.inkeysolutions.com/2011/04/generating-comma-separated-list-through.html]This[/url] link also shares some useful information about generating comma/delimiter separated string.Fri, 26 Oct 2012 03:40:25 GMTvinaypugaliahttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]WayneS (2/28/2011)[/b][hr][quote][b]CirquedeSQLeil (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr]Great! Thanks for the help on this. Sorry about the brain-fart on the 'value' thing, I was out all last week sick with vertigo so that's my excuse. :-D[/quote]No problem and you are welcome.[/quote]Jason, thanks for covering me on this... this thread never popped up on the recent posts, and I didn't see any activity until I got home and could check the email...Again, thanks![/quote]NPTue, 01 Mar 2011 07:47:09 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]CirquedeSQLeil (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr]Great! Thanks for the help on this. Sorry about the brain-fart on the 'value' thing, I was out all last week sick with vertigo so that's my excuse. :-D[/quote]No problem and you are welcome.[/quote]Jason, thanks for covering me on this... this thread never popped up on the recent posts, and I didn't see any activity until I got home and could check the email...Again, thanks!Mon, 28 Feb 2011 18:53:30 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]gregory.anderson (2/28/2011)[/b][hr]Great! Thanks for the help on this. Sorry about the brain-fart on the 'value' thing, I was out all last week sick with vertigo so that's my excuse. :-D[/quote]No problem and you are welcome.Mon, 28 Feb 2011 14:46:48 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxGreat! Thanks for the help on this. Sorry about the brain-fart on the 'value' thing, I was out all last week sick with vertigo so that's my excuse. :-DMon, 28 Feb 2011 14:44:46 GMTgregory.andersonhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspxThat is referring to the actual structure of the xml and not the retrieval of the data.Mon, 28 Feb 2011 12:05:48 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]CirquedeSQLeil (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr][quote][b]CirquedeSQLeil (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr][quote][b]UMG Developer (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr]I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:[code]For XML Path(''), Type).Value('(./text())[1]','varchar(max)')[/code]And this one:[code]For XML Path(''), TYPE).Value('.','varchar(max)')[/code]But I don't think those have any bearing on the 'Value' syntax.[/quote]I think your problem is that the XML commands are case sensative, and your .Value needs to be [b].value[/b].[/quote]Still a no-go, here's the entire CTE portion of my query:[code]Select CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CommentData = Stuff( ( Select ' ' + value From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL And cc.CustomerNumber = CTE.CustomerNumber And cc.CustomerCredentialID = CTE.CustomerCredentialID And lr.LicenseRestrictionID = CTE.LicenseRestrictionID Order By value --For XML Path(''), Type).Value('(./text())[1]','varchar(max)') For XML Path(''), TYPE).value('.','varchar(max)') ,1,1,'')From CTE[/code][/quote]Can you post the CTE portion of your script? The script you have posted is the select from the cte table, but we don't see the CTE defined.[/quote]Woops, here you go:[code]Declare @Today DateTimeSet @Today = GetDate();WITH CTE As( Select cc.CustomerNumber, cc.CustomerCredentialID, lr.LicenseRestrictionID, lr.Comment From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL)Select CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CommentData = Stuff( ( Select ' ' + value From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL And cc.CustomerNumber = CTE.CustomerNumber And cc.CustomerCredentialID = CTE.CustomerCredentialID And lr.LicenseRestrictionID = CTE.LicenseRestrictionID Order By value For XML Path(''), Type).value('(./text())[1]','varchar(max)')-- For XML Path(''), TYPE).value('.','varchar(max)') ,1,1,'')From CTEOrder By CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CTE.Comment;[/code]Edit:My guess is that it's something to do with the ' ' + value portion since I only want the values from the lr.Comment field, but not sure how to specify that in the ' ' + value portion...[/quote]It is choking on the column name of "Value" since you do not have a column named "Value" in your CTE or in your materialized table.Replace the [code="sql"] Select ' ' + Value[/code]to[code="sql"] Select ' ' + lr.Comment[/code]and change[code="sql"]Order By Value[/code]to[code="sql"]Order By lr.Comment[/code][/quote]I guess that makes sense, but why then does the article state this, but the 'value' works for the scenario in the article?:[quote]Since the field is ',' + Value (an unnamed expression), there is no name for the individual elements. What is left is a list of values, with each value prefixed with a comma. The TYPE clause specifies to return the data as an XML type. The .value('.','varchar(max)') takes each value, and converts it into a varchar(max) data type. The combination of the TYPE and .value means that values are created at XML tags (such as the ampersand (&), and the greater than (>) and less than (<) signs), will not be tokenized into their XML representations and will remain as is.[/quote]Mon, 28 Feb 2011 11:58:37 GMTgregory.andersonhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]gregory.anderson (2/28/2011)[/b][hr][quote][b]CirquedeSQLeil (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr][quote][b]UMG Developer (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr]I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:[code]For XML Path(''), Type).Value('(./text())[1]','varchar(max)')[/code]And this one:[code]For XML Path(''), TYPE).Value('.','varchar(max)')[/code]But I don't think those have any bearing on the 'Value' syntax.[/quote]I think your problem is that the XML commands are case sensative, and your .Value needs to be [b].value[/b].[/quote]Still a no-go, here's the entire CTE portion of my query:[code]Select CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CommentData = Stuff( ( Select ' ' + value From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL And cc.CustomerNumber = CTE.CustomerNumber And cc.CustomerCredentialID = CTE.CustomerCredentialID And lr.LicenseRestrictionID = CTE.LicenseRestrictionID Order By value --For XML Path(''), Type).Value('(./text())[1]','varchar(max)') For XML Path(''), TYPE).value('.','varchar(max)') ,1,1,'')From CTE[/code][/quote]Can you post the CTE portion of your script? The script you have posted is the select from the cte table, but we don't see the CTE defined.[/quote]Woops, here you go:[code]Declare @Today DateTimeSet @Today = GetDate();WITH CTE As( Select cc.CustomerNumber, cc.CustomerCredentialID, lr.LicenseRestrictionID, lr.Comment From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL)Select CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CommentData = Stuff( ( Select ' ' + value From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL And cc.CustomerNumber = CTE.CustomerNumber And cc.CustomerCredentialID = CTE.CustomerCredentialID And lr.LicenseRestrictionID = CTE.LicenseRestrictionID Order By value For XML Path(''), Type).value('(./text())[1]','varchar(max)')-- For XML Path(''), TYPE).value('.','varchar(max)') ,1,1,'')From CTEOrder By CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CTE.Comment;[/code]Edit:My guess is that it's something to do with the ' ' + value portion since I only want the values from the lr.Comment field, but not sure how to specify that in the ' ' + value portion...[/quote]It is choking on the column name of "Value" since you do not have a column named "Value" in your CTE or in your materialized table.Replace the [code="sql"] Select ' ' + Value[/code]to[code="sql"] Select ' ' + lr.Comment[/code]and change[code="sql"]Order By Value[/code]to[code="sql"]Order By lr.Comment[/code]Mon, 28 Feb 2011 11:55:50 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]CirquedeSQLeil (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr][quote][b]UMG Developer (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr]I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:[code]For XML Path(''), Type).Value('(./text())[1]','varchar(max)')[/code]And this one:[code]For XML Path(''), TYPE).Value('.','varchar(max)')[/code]But I don't think those have any bearing on the 'Value' syntax.[/quote]I think your problem is that the XML commands are case sensative, and your .Value needs to be [b].value[/b].[/quote]Still a no-go, here's the entire CTE portion of my query:[code]Select CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CommentData = Stuff( ( Select ' ' + value From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL And cc.CustomerNumber = CTE.CustomerNumber And cc.CustomerCredentialID = CTE.CustomerCredentialID And lr.LicenseRestrictionID = CTE.LicenseRestrictionID Order By value --For XML Path(''), Type).Value('(./text())[1]','varchar(max)') For XML Path(''), TYPE).value('.','varchar(max)') ,1,1,'')From CTE[/code][/quote]Can you post the CTE portion of your script? The script you have posted is the select from the cte table, but we don't see the CTE defined.[/quote]Woops, here you go:[code]Declare @Today DateTimeSet @Today = GetDate();WITH CTE As( Select cc.CustomerNumber, cc.CustomerCredentialID, lr.LicenseRestrictionID, lr.Comment From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL)Select CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CommentData = Stuff( ( Select ' ' + value From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL And cc.CustomerNumber = CTE.CustomerNumber And cc.CustomerCredentialID = CTE.CustomerCredentialID And lr.LicenseRestrictionID = CTE.LicenseRestrictionID Order By value For XML Path(''), Type).value('(./text())[1]','varchar(max)')-- For XML Path(''), TYPE).value('.','varchar(max)') ,1,1,'')From CTEOrder By CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CTE.Comment;[/code]Edit:My guess is that it's something to do with the ' ' + value portion since I only want the values from the lr.Comment field, but not sure how to specify that in the ' ' + value portion...Mon, 28 Feb 2011 11:35:07 GMTgregory.andersonhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]gregory.anderson (2/28/2011)[/b][hr][quote][b]UMG Developer (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr]I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:[code]For XML Path(''), Type).Value('(./text())[1]','varchar(max)')[/code]And this one:[code]For XML Path(''), TYPE).Value('.','varchar(max)')[/code]But I don't think those have any bearing on the 'Value' syntax.[/quote]I think your problem is that the XML commands are case sensative, and your .Value needs to be [b].value[/b].[/quote]Still a no-go, here's the entire CTE portion of my query:[code]Select CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CommentData = Stuff( ( Select ' ' + value From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL And cc.CustomerNumber = CTE.CustomerNumber And cc.CustomerCredentialID = CTE.CustomerCredentialID And lr.LicenseRestrictionID = CTE.LicenseRestrictionID Order By value --For XML Path(''), Type).Value('(./text())[1]','varchar(max)') For XML Path(''), TYPE).value('.','varchar(max)') ,1,1,'')From CTE[/code][/quote]Can you post the CTE portion of your script? The script you have posted is the select from the cte table, but we don't see the CTE defined.Mon, 28 Feb 2011 11:34:06 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]UMG Developer (2/28/2011)[/b][hr][quote][b]gregory.anderson (2/28/2011)[/b][hr]I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:[code]For XML Path(''), Type).Value('(./text())[1]','varchar(max)')[/code]And this one:[code]For XML Path(''), TYPE).Value('.','varchar(max)')[/code]But I don't think those have any bearing on the 'Value' syntax.[/quote]I think your problem is that the XML commands are case sensative, and your .Value needs to be [b].value[/b].[/quote]Still a no-go, here's the entire CTE portion of my query:[code]Select CTE.CustomerNumber, CTE.CustomerCredentialID, CTE.LicenseRestrictionID, CommentData = Stuff( ( Select ' ' + value From IADS..LicenseRestriction lr Inner Join IADS..License l On l.LicenseID = lr.LicenseID Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID And (cc.EndDate IS NULL Or cc.EndDate > @Today) And cc.CustomerCredentialDispositionID IS NULL Where lr.RestrictionCodeID = -8 And lr.Comment IS NOT NULL And cc.CustomerNumber = CTE.CustomerNumber And cc.CustomerCredentialID = CTE.CustomerCredentialID And lr.LicenseRestrictionID = CTE.LicenseRestrictionID Order By value --For XML Path(''), Type).Value('(./text())[1]','varchar(max)') For XML Path(''), TYPE).value('.','varchar(max)') ,1,1,'')From CTE[/code]Mon, 28 Feb 2011 11:14:02 GMTgregory.andersonhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]gregory.anderson (2/28/2011)[/b][hr]I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:[code]For XML Path(''), Type).Value('(./text())[1]','varchar(max)')[/code]And this one:[code]For XML Path(''), TYPE).Value('.','varchar(max)')[/code]But I don't think those have any bearing on the 'Value' syntax.[/quote]I think your problem is that the XML commands are case sensative, and your .Value needs to be [b].value[/b].Mon, 28 Feb 2011 10:22:05 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx[quote][b]gregory.anderson (2/28/2011)[/b][hr]I have implemented this script but am getting the error "Invalid column name 'Value'" when running it. Is there something special that I need to do in order for Mgmt Studio to recognize the "unnamed expression" part of that? I've implemented both versions, this one:[code]For XML Path(''), Type).Value('(./text())[1]','varchar(max)')[/code]And this one:[code]For XML Path(''), TYPE).Value('.','varchar(max)')[/code]But I don't think those have any bearing on the 'Value' syntax.[/quote]No specific tricks, but maybe some piece of the code got lost in translation during the copy and paste.The .value function is a function of xquery and is supported in SSMS 2005 and up.[url]http://msdn.microsoft.com/en-us/library/ms178030.aspx[/url] for a bit more info on it.Mon, 28 Feb 2011 10:04:13 GMTSQLRNNR