SQL Query to get multiple records with a single different field

Question

I have a table (it's not a table for real but a result of another query) like this:

ID | NAME | OPTIONAL_ID | OPTIONAL_NAME | OTHER FIELDS
1  | abc  | 34          | optional 1    | ...
1  | abc  | 35          | optional 2    | ...
1  | abc  | 36          | optional 3    | ...
1  | abc  | 37          | optional 4    | ...
2  | qwe  | 34          | optional 1    | ...
2  | qwe  | 35          | optional 2    | ...
2  | qwe  | 36          | optional 3    | ...

What I need is to get a different result with a record for every object, in this example 'abc' and 'qwe' and a Column like OPTIONALS that contains all optional_name for each object.

It's not important for me the output type ( JSON,XML, query output)

I tried with 'group by' but i didn't get the desired result.

I guess you want something like abc | optional 1, optional 2, optional 3, optional 4, do you? — Michał Turczyn
– Michał Turczyn, Commented Feb 13, 2018 at 9:42
A column like that for each object: OPTIONALS optional1,optional2,optional3 or in JSON: {id:1,Name:'abc',Optionals:['optional1','optional2','optional3','optional4']} {id:2,Name:'qwe',Optionals:['optional1','optional2','optional3']} — Graziano Dimatteo
– Graziano Dimatteo, Commented Feb 13, 2018 at 9:44

Michał Turczyn · Accepted Answer · 2018-02-13 11:13:33Z

1

Try this (I considered only relevant columns):

declare @table table (name char(3), optional_name char(10))
insert into @table values
('abc', 'optional 1'),
('abc', 'optional 2'),
('abc', 'optional 3'),
('abc', 'optional 4'),
('qwe', 'optional 1'),
('qwe', 'optional 2'),
('qwe', 'optional 3')

select name,
       (select optional_name from @table where name = T.name for xml path(''), type)
from @table [T]
group by name

If you want JSON, then replace XML with JSON in last query (newest SQL Server required).

Another option (slightly modified previous query):

select name,
       (select optional_name + ', ' from @table where name = T.name for xml path(''), type).value('(/)[1]','varchar(2000)') [optional_name]
from @table [T]
group by name

edited Feb 13, 2018 at 11:13

answered Feb 13, 2018 at 9:49

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Graziano Dimatteo Over a year ago

this query returns to me all records with the optionals column but not in distinct mode and if I add distinct on select SQL get me an error for distinct usage on XML data.

Michał Turczyn Over a year ago

@GrazianoDimatteo Another solution added, if you would like another way :)

Salman Arshad · Accepted Answer · 2018-02-13 10:06:49Z

One solution is to use FOR XML to combine rows for each group:

CREATE TABLE #testdata (ID int, NAME varchar(3), OPTIONAL_ID int, OPTIONAL_NAME varchar(10));
INSERT INTO #testdata (ID, NAME, OPTIONAL_ID, OPTIONAL_NAME) VALUES
    (1, 'abc', 34, 'optional 1'),
    (1, 'abc', 35, 'optional 2'),
    (1, 'abc', 36, 'optional 3'),
    (1, 'abc', 37, 'optional 4'),
    (2, 'qwe', 34, 'optional 1'),
    (2, 'qwe', 35, 'optional 2'),
    (2, 'qwe', 36, 'optional 3');

WITH names AS (
    SELECT DISTINCT NAME
    FROM #testdata
)
SELECT *
FROM names
CROSS APPLY (
    SELECT OPTIONAL_NAME
    FROM #testdata
    WHERE names.NAME = #testdata.NAME
    FOR XML PATH(''), ROOT('OPTIONAL_NAMES'), TYPE
) AS ca(xmldata)

For comma separated list inside query output, use:

SELECT names.NAME, STUFF(xmldata, 1, 1, '')
FROM names
CROSS APPLY (
    SELECT ',' + OPTIONAL_NAME
    FROM #testdata
    WHERE names.NAME = #testdata.NAME
    FOR XML PATH('')
) AS ca(xmldata)

M.Ali · Accepted Answer · 2018-02-13 09:48:18Z

An XML Solution can look something like....

Test Data

CREATE TABLE #temp (ID INT , NAME VARCHAR(20), OPTIONAL_ID INT 
                    ,  OPTIONAL_NAME VARCHAR(20), OTHER_FIELDS VARCHAR(20))

INSERT INTO #temp VALUES 
(1  ,'abc'  , 34  ,'optional 1' , 'Some Data' ),
(1  ,'abc'  , 35  ,'optional 2' , 'Some Data' ),
(1  ,'abc'  , 36  ,'optional 3' , 'Some Data' ),
(1  ,'abc'  , 37  ,'optional 4' , 'Some Data' ),
(2  ,'qwe'  , 34  ,'optional 1' , 'Some Data' ),
(2  ,'qwe'  , 35  ,'optional 2' , 'Some Data' ),
(2  ,'qwe'  , 36  ,'optional 3' , 'Some Data' );

Query

SELECT
           t.[ID] AS [@ID]
        ,  t.Name AS [@Name]
        , (  
            SELECT  t2.OPTIONAL_ID
                  , t2.OPTIONAL_NAME
                  , t2.OTHER_FIELDS
            FROM #temp t2
            WHERE t.ID = t2.ID
            FOR XML PATH('OPTIONALS') , TYPE
           )
FROM 
(
    SELECT DISTINCT [ID] , Name
    FROM #temp 
)t
FOR XML PATH('x')

XML OUTPUT

<x ID="1" Name="abc">
  <OPTIONALS>
    <OPTIONAL_ID>34</OPTIONAL_ID>
    <OPTIONAL_NAME>optional 1</OPTIONAL_NAME>
    <OTHER_FIELDS>Some Data</OTHER_FIELDS>
  </OPTIONALS>
  <OPTIONALS>
    <OPTIONAL_ID>35</OPTIONAL_ID>
    <OPTIONAL_NAME>optional 2</OPTIONAL_NAME>
    <OTHER_FIELDS>Some Data</OTHER_FIELDS>
  </OPTIONALS>
  <OPTIONALS>
    <OPTIONAL_ID>36</OPTIONAL_ID>
    <OPTIONAL_NAME>optional 3</OPTIONAL_NAME>
    <OTHER_FIELDS>Some Data</OTHER_FIELDS>
  </OPTIONALS>
  <OPTIONALS>
    <OPTIONAL_ID>37</OPTIONAL_ID>
    <OPTIONAL_NAME>optional 4</OPTIONAL_NAME>
    <OTHER_FIELDS>Some Data</OTHER_FIELDS>
  </OPTIONALS>
</x>
<x ID="2" Name="qwe">
  <OPTIONALS>
    <OPTIONAL_ID>34</OPTIONAL_ID>
    <OPTIONAL_NAME>optional 1</OPTIONAL_NAME>
    <OTHER_FIELDS>Some Data</OTHER_FIELDS>
  </OPTIONALS>
  <OPTIONALS>
    <OPTIONAL_ID>35</OPTIONAL_ID>
    <OPTIONAL_NAME>optional 2</OPTIONAL_NAME>
    <OTHER_FIELDS>Some Data</OTHER_FIELDS>
  </OPTIONALS>
  <OPTIONALS>
    <OPTIONAL_ID>36</OPTIONAL_ID>
    <OPTIONAL_NAME>optional 3</OPTIONAL_NAME>
    <OTHER_FIELDS>Some Data</OTHER_FIELDS>
  </OPTIONALS>
</x>

Collectives™ on Stack Overflow

SQL Query to get multiple records with a single different field

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related