Different SQL Select query

Question

I need to write SQL query in order to extract some data.

i have this data in my table:

ID  Store   Value
1    9921    NOK
2    9921    NOK1
3    9921    OK3

what i need is to get data from select query like this form:

9921         NOK,NOK1,OK3

Any help please ?

What database engine? To do that on Apache Derby I had to write a user-defined aggregator that concatenated strings. Then I could run `select store, uda(value) where store=9921`. — cxw, Nov 09 '15 at 10:19
Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Evaldas Buinauskas, Nov 09 '15 at 12:17

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

You can use STUFF:

SELECT DISTINCT Store,
       STUFF((SELECT ',' + Value
              FROM Your_Table
              WHERE Store = 9921
              FOR XML PATH('')), 1, 1, '')
FROM Your_Table

edited May 23 '17 at 12:15

Community

1
1

answered Nov 09 '15 at 10:55

Nguyễn Hải Triều

1,454
1
8
14

1

Just so you're aware, the magical concatenation is achieved by the `FOR XML PATH('')` bit of that query. All that `STUFF` is doing is removing a leading comma. Referring to this as "using STUFF" is to hugely miss the point of how that query works. – Damien_The_Unbeliever Nov 09 '15 at 11:57

score 0 · Answer 2 · answered Nov 09 '15 at 11:41

Try to accomplish your excepted output by using COALESCE;

Create a sample table for testing purpose

CREATE TABLE SampleData (id INT ,store INT ,value NVARCHAR(50))

INSERT INTO SampleData VALUES (1 ,9921 ,'NOK')
INSERT INTO SampleData VALUES (2 ,9921 ,'NOK1')
INSERT INTO SampleData VALUES (3 ,9921 ,'NOK2')

Create a Scalar-Valued Function

Alter FUNCTION fun_GetCombinedData 
(
  @store int
)
RETURNS nvarchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @CombineValue nvarchar(max)

SELECT @CombineValue = COALESCE(@CombineValue + ', ', '') + value 
FROM SampleData where store=@store

RETURN @CombineValue

END
GO

Final Query,

SELECT store
,dbo.fun_GetCombinedData(store) AS value
FROM SampleData
GROUP BY store

Expected Output:

store   |    value
------------------------
9921    |  NOK,NOK1,NOK2

This is one of the way to simplify your select query.

score 0 · Answer 3 · answered Nov 09 '15 at 11:47

Using T-SQL we can do it this way:

declare @store int = 9921, @values varchar(max) = ''

select @values = @values
     + case
         when @values = '' then ''
         else ','
       end + value
  from table_name
 where store = @store
 order by id

select @store, @values

Anup Shah · Accepted Answer · 2019-06-27T11:27:30.697

-1

Go through this below example

Demo: [SQLFiddle]

The SQL I used is as below,

SELECT
 store,
 STUFF(
     (SELECT DISTINCT ',' + value
      FROM SampleData
      WHERE store = a.store
      FOR XML PATH (''))
      , 1, 1, '')  AS CombineValues
FROM SampleData AS a
GROUP BY store

you will see your expected result as "CombineValues"

store   CombineValues
9921    NOK,NOK1,NOK2

edited Jun 27 '19 at 11:27

answered Nov 09 '15 at 12:15

Anup Shah

167
1
13

This answer could do with including (all/the pertinent part of) the actual code. We frown on "link only" answers here, where it's not possible to make sense of the answer by just reading it. – Damien_The_Unbeliever Nov 10 '15 at 09:34
come on, what's wrong here? I had the same difficulty once in my project and here is the answer, is it wrong solution? – Anup Shah Nov 13 '15 at 13:19

Different SQL Select query

4 Answers4