Result of subquery as Columns

Question

I have a table with some terminologies. These terminologies are in several languages. For Example:

SELECT *
FROM Terminology

results in

ID   language          text        create_date
1       DE          TextDE  2011-03-03 14:31:28.423
1       EN          TextEN  2011-04-03 14:32:27.423
1       ES          TextES  2011-05-03 14:33:26.423
1       FR          TextFR  2011-06-03 14:34:25.423
1       NL          TextNL  2011-07-03 14:35:24.423
1       PT          TextPT  2011-08-03 14:36:23.423
2       DE                 ...
...

But i need the data in the following form:

ID  DE     EN     ES     FR     NL     PT
1 TextDE TextEN TextES TextFR TEXTNL TextPT
2 ...

ID and language are building the primary key. The number of languages is variable, so it's not possible to add just 6 additional columns.

Is it somehow possible to get this done with a plain SQL-Query or do i have to sort this in my program?

EDIT:
with a column in the table that is not selected, but has different values, i get several rows.
So like in the example with 6 different dates i get 6 different rows:

ID          DE         EN         ES         FR         NL         PT

1   TextDE            NULL       NULL       NULL       NULL       NULL
1   NULL              TextEN     NULL       NULL       NULL       NULL
1   NULL              NULL       TextES     NULL       NULL       NULL
1   NULL    NULL    NULL    TextFr NULL NULL    NULL
...

Can i somehow turn this to one row?

You can get result: click [here](http://stackoverflow.com/questions/3231312/sql-row-value-as-column-name) and also [this](http://stackoverflow.com/questions/12989306/sql-server-row-values-as-column-names-pivot-table) — Kavipriya, Aug 14 '13 at 07:16
Seems that you need MySql group_concat function http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Anda Iancu, Aug 14 '13 at 07:18

Devart · Accepted Answer · 2013-08-14T10:02:10.380

Try this one -

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp (
      ID INT
    , [language] CHAR(2)
    , [text] VARCHAR(10)
    , create_date DATETIME
)
INSERT INTO #temp (ID, [language], [text], create_date)
VALUES 
    (1, 'DE', 'TextDE', '2011-03-03 14:31:28.423'),
    (1, 'EN', 'TextEN', '2011-04-03 14:32:27.423'),
    (1, 'ES', 'TextES', '2011-05-03 14:33:26.423'),
    (1, 'FR', 'TextFR', '2011-06-03 14:34:25.423'),
    (1, 'NL', 'TextNL', '2011-07-03 14:35:24.423'),
    (1, 'PT', 'TextPT', '2011-08-03 14:36:23.423'),
    (2, 'PT', 'TextPT', NULL)

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
    SELECT ID, DE, EN, ES, FR, NL, PT
    FROM (
        SELECT ID, [language], [text]
        FROM #temp
    ) t
    PIVOT (
        MAX([text]) 
        FOR [language] IN (' + 
        STUFF((
            SELECT DISTINCT ', [' + t.[language] + ']'
            FROM #temp t
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') + ')
    ) pv'

PRINT @SQL
EXEC sys.sp_executesql @SQL

Output -

ID          DE         EN         ES         FR         NL         PT
----------- ---------- ---------- ---------- ---------- ---------- ----------
1           TextDE     TextEN     TextES     TextFR     TextNL     TextPT
2           NULL       NULL       NULL       NULL       NULL       TextPT

really great solution! but i have one more problem. There are additional columns with dates which are different in every column and even when they are not read, i get a row for each different date. So, 6 languages, 6 different dates => 6 rows. Is it possible to get just one row? — Obl Tobl, Aug 14 '13 at 08:43
@Obl Tobl please provide a small example for this situation. — Devart, Aug 14 '13 at 09:00

score 1 · Answer 2 · edited May 23 '17 at 10:31

1

there's no easy way to do this in SQL Server, see this answer for general solution - https://stackoverflow.com/a/13583258/1744834

for small number of columns you could do:

select
    ID,
    max(case when language = 'DE' then text else null end) as TextDE,
    max(case when language = 'EN' then text else null end) as TextEN,
    ...
from Terminology
group by ID

edited May 23 '17 at 10:31

Community

1
1

answered Aug 14 '13 at 07:27

Roman Pekar

107,110
28
195
197

Result of subquery as Columns

2 Answers2