SQL Select multi items as something where there values = 1,2 and 3

Question

So i would like to select a few values separated by a comma into a comment field where the values equal certain matches.

Here is how my table looks.

[dbo].[ValuationDetail](
[ValuationDetailId] [int] IDENTITY(1,1) NOT NULL,
[ValuationId] [int] NOT NULL,
[TagName] [varchar](100) NOT NULL,
[TagValue] [varchar](max) NULL,
[Mod] [varchar](50) NOT NULL,
[IncludePdf] [bit] NOT NULL,
[IsBinary] [bit] NOT NULL,

I would like to do something like.

SELECT 
  ValuationId,
  TagValue as Comment WHERE TagName = 'Sale_8_township', 'Sale_8_portion', 'Sale_8_erf'
WHERE ValuationId = 21702

Result being

Comment = ST HELENA BAY,0,1719

Please let me know if you need more info.

[`WHERE TagName IN (Tag1, Tag2, Tag3)`](https://msdn.microsoft.com/en-us/library/ms177682.aspx) — Cristian Lupascu, Oct 29 '15 at 10:59
How can a single value be equal with three different values? You mean `1,2 or 3` not _and_. — Tim Schmelter, Oct 29 '15 at 11:00
SELECT ValuationId, TagValue as Comment WHERE ValuationId = 123 AND TagName IN (Tag1, Tag2, Tag3) — Adish, Oct 29 '15 at 11:05
I would like to build a comment field from the value fields where there name equals different values. — Pomster, Oct 29 '15 at 11:22
Added a picture and an example of the result i'm looking for. Hope that helps. — Pomster, Oct 29 '15 at 11:29
Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Tab Alleman, Oct 29 '15 at 13:24

score 0 · Answer 1 · answered Oct 29 '15 at 11:05

0

You need something like GROUP_CONCAT of MySQL

SELECT 
  ValuationId,
  GROUP_CONCAT(TagValue) as Comment 
FROM table
WHERE TagName IN (Tag1, Tag2, Tag3)
AND ValuationId = 123
GROUB BY ValuationId

if you don't want to create a new function in SQL Server, you can workaround with the STUFF built in function :

STUFF(
         (SELECT ', ' + TagValue
          FROM table
          WHERE ValuationId = outer. ValuationId
          FOR XML PATH (''))
          , 1, 1, '')  AS Comment

answered Oct 29 '15 at 11:05

aleroot

71,077
30
176
213

*aleroot Group_Concat is not recognized as a built in function name. and i am not understanding the Stuff. STUFF( (SELECT ', ' + TagValue FROM ValuationDetail WHERE ValuationId = 21701 AND TagName = 'comparable_sale_1_sales_price' OR 'comparable_sale_2_sale_price' OR 'comparable_sale_3_sale_price' FOR XML PATH ('')) , 1, 1, '') AS Comment – Pomster Oct 29 '15 at 11:19
*aleroot Please look at my edit to get a better idea of what i am trying to achieve – Pomster Oct 29 '15 at 11:31
@Pomster Follow the link on codeplex and add the group_concat function ... http://groupconcat.codeplex.com – aleroot Oct 29 '15 at 11:34

score 0 · Answer 2 · answered Oct 30 '15 at 11:41

Coalesce helped me concatenate the string stored in a temp table.

SELECT @Comment = COALESCE(@Comment + ',','') + TagValue FROM #TempComment

CREATE TABLE #TempComment (id int, TagValue varchar(500))

INSERT INTO #TempComment
        SELECT
            CASE
                WHEN TagName = @saleErf THEN  1
                WHEN  TagName = @salePortion THEN 2
                WHEN  TagName = @saleTownship THEN 3
                WHEN  TagName = @salePrice THEN 4
                WHEN  TagName = @saleDate THEN 5
                WHEN  TagName = @saleOverall THEN 6
            END,
            TagValue
        FROM ValuationDetail
        WHERE TagName IN (@saleErf,@salePortion,@saleTownship, @salePrice, @saleDate, @saleOverall) AND ValuationId = @ValuationId

SELECT @Comment = COALESCE(@Comment + ',','') + TagValue FROM #TempComment ORDER BY Id

SQL Select multi items as something where there values = 1,2 and 3

2 Answers2