November 26, 2008 at 2:08 pm
Hi! I'd like to perform the following query
Select Name,
( SELECT ali.ItemValue
FROM AttributeValues as av
INNER JOIN AttributeListItem as ali
ON av.AttributeListID = ali.AttributeListItemID
WHERE (((av.EntityID)=p.ProcessID) AND ((av.AttributeID)=28))
) TCAttributesCheckbox
From Process as p
The only problem is, my embedded select statement returns 3 rows so I get an error that says I'm trying to insert 3 things. I'm trying to do this in a view without first putting the data in a variable, is there some way I could concatinate the 3 items into a comma delimited list for display here so the output would look like this
Column1 Column2
Name Item1, Item2, Item3
November 26, 2008 at 3:15 pm
OK, maybe this is a silly question, but in your subquery:
SELECT ali.ItemValue
FROM AttributeValues as av
INNER JOIN AttributeListItem as ali
ON av.AttributeListID = ali.AttributeListItemID
WHERE (((av.EntityID)=p.ProcessID) AND ((av.AttributeID)=28))
does av.AttributeListID really join to ali.AttributeListItemID or is this just an odd nomenclature issue?
Anyway, below is an interesting article which talks about how you can create a comma separated list with some XML trickery in your query:
http://code.msdn.microsoft.com/SQLExamples/wiki/view.aspx?title=createacommadelimitedlist
November 26, 2008 at 3:59 pm
Interesting... I tried the example shown and am getting an error with it. Here's my query
SELECT
p.processID,
Text = substring(SELECT ( ', ' + ali.ItemValue)
FROM AttributeValues as av
INNER JOIN AttributeListItem as ali
ON av.AttributeListID = ali.AttributeListItemID
WHERE (((av.EntityID)=p.ProcessID) AND ((av.AttributeID)=28))
For XML PATH( '' ), 3, 1000)
FROM Process as p
DBVisualizer is returning
15:53:45 [SELECT - 0 row(s), 0.000 secs] [Error Code: 156, SQL State: HY000] [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near the keyword 'SELECT'.
Could it be erroring out because I'm using a SQLServer 2000 driver? I assume the select keyword in question has to do with the subquery...
Oh, and to answer your question, Yes, it is a quirk in the naming convention of the system. 😀 My defense. I just use the tables, I don't create them! 😉
November 27, 2008 at 2:58 am
Anith Sen collected together a lot of techniques to do this sort of delimited list in this article here http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ which is well worth a look. Many of these will work in SQL Server 2000.
Best wishes,
Phil Factor
November 27, 2008 at 10:29 pm
... and here's some things to watch out for...
[font="Arial Black"]Performance Tuning: Concatenation Functions and Some Tuning Myths[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2008 at 1:36 pm
Alright! I'm making progress! Using your suggestions, I opted to use the For XML Path and it is almost working. I just need someone to help out with one little problem I'm having.
First, here's the data I'm working with
AttributeValues Table
AttributeValuesID AttributeID EntityID AttributeListID
460 28 218 34
461 28 218 36
462 28 218 38
AttributeListItem Table
AttributeListeItemID AttributeID ItemValue
34 28 System Test
35 28 Integration Test
36 28 Functional Test
37 28 Load Test
38 28 Security Test
39 28 Regression Test
I want to get just the ItemValues attached to EntityID 218 and AttributeID 28 so my query is like this
SELECT distinct(ali.ItemValue + ',')
FROM AttributeValues av
inner Join AttributeListItem ali
on ali.AttributeID = av.AttributeID
WHERE av.AttributeID = 28 and av.EntityID = 218
FOR XML PATH('')
However, this returns
Functional Test,Integration Test,Load Test,Regression Test,Security Test,Stress Test,
I need it to return only what is attached to entityid 218 which is System Test, Functional Test, and Security Test.
I'd also like to have them displayed with a carriage return after each item like so
System Test
Functional Test
Security Test
But when I use ali.ItemValue + char(13) I get this
Functional Test Integration Test Load Test Regression Test Security Test Stress Test System Test
Any help on both of these would be appreciated! Thank you!
December 3, 2008 at 1:41 pm
But when I use ali.ItemValue + char(13) I get this
Functional Test Integration Test Load Test Regression Test Security Test Stress Test System Test
hmm... the formatting didn't come through on the post because it prints out an html format code of &#xoD between each string.
December 3, 2008 at 2:54 pm
I've also been able to get the right details by doing it this way
SELECT MAX( CASE AttributeListID WHEN 34 THEN ItemValue + ', ' ELSE '' END ) + -- Test:34 Production:15 System Test
MAX( CASE AttributeListID WHEN 35 THEN ItemValue + ', ' ELSE '' END ) + -- Test:35 Production:none Integration Test
MAX( CASE AttributeListID WHEN 36 THEN ItemValue + ', ' ELSE '' END ) + -- Test:36 Production:none Functional Test
MAX( CASE AttributeListID WHEN 37 THEN ItemValue + ', ' ELSE '' END ) + -- Test:37 Production:none Load Test
MAX( CASE AttributeListID WHEN 38 THEN ItemValue + ', ' ELSE '' END ) + -- Test:38 Production:7 Security Test
MAX( CASE AttributeListID WHEN 39 THEN ItemValue + ', ' ELSE '' END ) + -- Test:39 Production:10 Regression Test
MAX( CASE AttributeListID WHEN 53 THEN ItemValue ELSE '' END ) --Test:53 Production: Stress Test
FROM AttributeValues as av
Join AttributeListItem as ali
on (ali.AttributeListItemID = av.AttributeListID)
Where av.EntityID = 218
This returns
System Test, Functional Test, Security Test,
which is excellent, the problem is, I use it as a select criteria in a larger sql statement and when I execute it in the larger statement, I get the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression."
Is there some way to trick the super query into thinking this is just one row like it really is?
Example of what I mean by a super query
select u.login,
([insert sql statement above here]) userorg
from dps_user u;
December 4, 2008 at 4:13 pm
ha ha! I figured it out! here's how I solved it!
SELECT TOP 10 PERCENT MAX(CASE AttributeListID WHEN 7 THEN ItemValue + ', ' ELSE '' END) + -- Test:34 Production:7 System Test
MAX( CASE AttributeListID WHEN 10 THEN ItemValue + ', ' ELSE '' END ) + -- Test:35 Production:10 Integration Test
MAX( CASE AttributeListID WHEN 11 THEN ItemValue + ', ' ELSE '' END ) + -- Test:36 Production:11 Functional Test
MAX( CASE AttributeListID WHEN 13 THEN ItemValue + ', ' ELSE '' END ) + -- Test:37 Production:13 Load Test
MAX( CASE AttributeListID WHEN 14 THEN ItemValue + ', ' ELSE '' END ) + -- Test:38 Production:14 Security Test
MAX( CASE AttributeListID WHEN 15 THEN ItemValue + ', ' ELSE '' END ) + -- Test:39 Production:15 Regression Test
MAX( CASE AttributeListID WHEN 16 THEN ItemValue ELSE '' END ) --Test:53 Production:16 Stress Test
FROM AttributeValues as av
Join AttributeListItem as ali
on (ali.AttributeListItemID = av.AttributeListID)
Where av.EntityID = p.ProcessID
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply