COALESCE STRING IN sql STATEMENT

Question

I have a COALESCE statement extracting data from a table:

DECLARE @Output NVARCHAR(MAX)

SELECT @Output = COALESCE(@Output + ', ', '') +  '''' + tCCGroup + '''' 
FROM tblActivityPerPPCC_Matrix
WHERE tPPCC = 'NSEA0101'

SELECT @Output OUTPUT

with the following output.

'FADT', 'FD10RB', 'WA600'

I want to insert this output string (which is now in the variable @Output) into the following statement:

SELECT * FROM XYZ
WHERE Column_Name IN (<< THE OUTPUT FROM COALESCE >>)

to be similar as

SELECT * FROM XYZ
WHERE Column_Name IN ('FADT', 'FD10RB', 'WA600')

This statement will be turned into a dynamic statement in a Cursor

Any help would be appreciated.

Pierre

You can use the answer to this: http://stackoverflow.com/questions/24540337/pass-comma-separated-number-to-in-clause-in-stored-procedure/24543236#24543236 — Bob, Jul 08 '14 at 12:07
SELECT * FROM XYZ WHERE Column_Name IN ( SELECT tCCGroup FROM tblActivityPerPPCC_Matrix WHERE tPPCC = 'NSEA0101' ) — Jeremy Danyow, Jul 08 '14 at 12:09
I added the SQL Server tag as it looks like that type of syntax — Nick.Mc, Jul 08 '14 at 12:13
why would you take a set of rows in a table to a comma separated string instead of just using the rows in the table? bizarre — Paul Maxwell, Jul 08 '14 at 12:14
So, you have a (potentially broken) routine which takes *multiple* values and crams them into a single string, and then you want to take that string and (as it turns out) split that back into separate values. — Damien_The_Unbeliever, Jul 08 '14 at 12:16

score 1 · Answer 1 · answered Jul 08 '14 at 12:13

According the description what you have mentioned, you dont need to create temperary variables. You can just use a subquery for this.

SELECT * 
FROM XYZ 
WHERE Column_Name IN ( SELECT tCCGroup 
                       FROM tblActivityPerPPCC_Matrix 
                       WHERE tPPCC = 'NSEA0101' )

score 0 · Answer 2 · answered Jul 08 '14 at 12:29

Perhaps I've misunderstood the question but to incorporate that string into a dynamically produce query would look something like this.

DECLARE
          @Output VARCHAR(MAX)
        , @SQL  VARCHAR(MAX)
        , @Table_name varchar(64) 
        , @Column_name varchar(64) 

set @Table_name = 'test'
set @Column_name = 'xyz'

SELECT @Output = COALESCE(@Output + ', ', '') +  '''' + tCCGroup + '''' 
FROM tblActivityPerPPCC_Matrix
WHERE tPPCC = 'NSEA0101'

set @sql = 'SELECT * '
           + ' FROM ' 
           + @Table_name
           + ' WHERE ' 
           + @Column_Name
           + ' IN (' 
           + @Output
           + ')'

select @sql
-- exec (@sql)

score 0 · Answer 3 · answered Jul 08 '14 at 14:39

0

Yikes. Why do you need to run dynamic sql inside a cursor? From the very vague post I see no need for a cursor at all. If you can explain what you are trying to do and post some ddl and sample data (or sql fiddle) I would be happy to help you remove the cursor from your process.

answered Jul 08 '14 at 14:39

Sean Lange

33,028
3
25
40

My request was premature. See this new post: [link](http://stackoverflow.com/questions/24693119/where-field1-in-null/24693174#24693174) – Pierre Cornelissen Jul 11 '14 at 08:29

COALESCE STRING IN sql STATEMENT

3 Answers3