June 15, 2015 at 12:03 pm
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list.
You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
June 15, 2015 at 12:21 pm
Try:
SELECT
preop.Case_ID,
STUFF( (SELECT ',[' + s.Name + ']'
FROM dbo.TBL_Preop as s
WHERE
s.Case_ID = preop.Case_ID and
s.Deleted_CD = 0
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '')
FROM dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
Hope this helps.
June 15, 2015 at 12:38 pm
Gives me an error message ( I did modify your syntax )
SELECT
preop.MPOG_Case_ID,
STUFF( (SELECT ',[' + s.AIMS_value_text + ']'
FROM dbo.AIMS_Preop as s
WHERE
s.MPOG_Case_ID = preop.MPOG_Case_ID
and
s.MPOG_Deleted_CD = 0
and
preop.AIMS_Preop_Concept_ID IN (4693,12161)
and
(AIMS_VALUE_TEXT like '%DVT%' COLLATE Latin1_General_BIN )
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '') as DVT
FROM dbo.AIMS_Preop preop
WHERE
preop.MPOG_Deleted_CD = 0
and
preop.AIMS_Preop_Concept_ID IN (4693,12161)
GROUP BY
preop.MPOG_Case_ID
The error message is
Msg 8120, Level 16, State 1, Line 10
Column 'dbo.AIMS_Preop.AIMS_Preop_Concept_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 10
Column 'dbo.AIMS_Preop.AIMS_Preop_Concept_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
June 15, 2015 at 12:50 pm
This works, but I need to add one more item to the inner where clause. As soon as I do that I get that above error
SELECT
preop.MPOG_Case_ID,
STUFF( (SELECT ',[' + s.AIMS_value_text + ']'
FROM dbo.AIMS_Preop as s
WHERE
s.MPOG_Case_ID = preop.MPOG_Case_ID and
s.MPOG_Deleted_CD = 0 /* and */
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '')
FROM dbo.AIMS_Preop preop
WHERE
preop.MPOG_Deleted_CD = 0
GROUP BY
preop.MPOG_Case_ID
Does not allow me to add ---> preop.AIMS_Preop_Concept_ID IN (4693,12161) to the inner where clause
June 15, 2015 at 12:53 pm
Try s.AIMS_Preop_Concept_ID instead preop.AIMS_Preop_Concept_ID.
June 15, 2015 at 12:58 pm
Thanks
It worked...
You deserve a beer!
June 15, 2015 at 1:09 pm
Works fine except for one little thing,
The first item will not start with the "["
That's about all. No big deal.
June 15, 2015 at 1:40 pm
Try:
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 1, '')
instead
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1, 2, '')
June 15, 2015 at 3:37 pm
Just an fyi since you figured out what was wrong, but it really helps if you format your code:
SELECT
preop.MPOG_Case_ID,
STUFF( (SELECT ',[' + s.AIMS_value_text + ']'
FROM
dbo.AIMS_Preop as s
WHERE
s.MPOG_Case_ID = preop.MPOG_Case_ID and
s.MPOG_Deleted_CD = 0 and
s.AIMS_Preop_Concept_ID IN (4693,12161) and
(s.AIMS_VALUE_TEXT like '%DVT%' COLLATE Latin1_General_BIN )
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1, 1, '') as DVT
FROM
dbo.AIMS_Preop preop
WHERE
preop.MPOG_Deleted_CD = 0 and
preop.AIMS_Preop_Concept_ID IN (4693,12161)
GROUP BY
preop.MPOG_Case_ID;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply