How to include empty values using pivot dinamically in a query to create relationship between column and row

Question

I have a query who returns the next results

+-----------+--------+-------+
| idPeriodo | codigo | valor |
+-----------+--------+-------+
|     1     |   7    |  1000 |
|     1     |   8    |  1000 | 
|     1     |   9    |  1000 | 
|     2     |   7    |  1000 | 
|     2     |   8    |  1000 | 
|     3     |   7    |  1000 | 
|     3     |   9    |  1000 |  
+-----------+--------+-------+

I need to organize the query because I'm creating a text plain file with the results, so in the first row I need to put each "codigo" split by tab, something like this.

--This is the first row, is like a head
    7   8   9

Afther this, I need to put the column "valor" associated with its respective codigo and idPeriodo. The final result must be something like this:

    7       8       9
1   1000    1000    1000
2   1000    1000    
3   1000            1000

Now I have two queries, the first one is to obtain the first row(head with codigo values) and second one I'm doing a query to obtain all values from column "valor" grouped by idPeriodo. These are my queries looks like.

-------Query to return head(codigo values)---------------
SELECT DISTINCT CONCAT(conjunto.codigo, central.confterm_id) as codigo_central 
FROM   mantenimiento_central_termica mantenimiento 
JOIN   configuracion_central_termica central 
ON     central.confterm_id = mantenimiento.confterm_id 
JOIN   conjunto_centrales conjunto
ON     central.id_conjuntocentral = conjunto.id

-------Query to return valor grouped by idPeriodo---------------
SELECT id_periodo , 
       valor_mantenimiento = string_agg(valor_mantenimiento, CHAR(9))
       WITHIN GROUP (ORDER BY codigo_central)
FROM(
SELECT CONCAT(conjunto.codigo, central.confterm_id) as codigo_central, 
       per_id                          AS id_periodo , 
       mantenimcentraltermica_potencia AS valor_mantenimiento 
FROM   mantenimiento_central_termica mantenimiento 
JOIN   configuracion_central_termica central 
ON     central.confterm_id = mantenimiento.confterm_id 
JOIN   conjunto_centrales conjunto
ON     central.id_conjuntocentral = conjunto.id
WHERE  cas_id = 2) main_thermal
GROUP BY id_periodo

The problem I have is the second query returns only the values from column valor who is not null and when I'm going to split the first query with the second one I obtain something like this:

    7       8       9
1   1000    1000    1000
2   1000    1000    
3   1000    2000    --The error is here, because the 2000 value must be in the third column, with the 9(codigo) value.

I need the query put a blank space when find a null valor in a relation between codigo and idPeriodo.

How Can i do that ?

This doesn't look like a `STRING_AGG` problem, but more like a pivot/cross tab. — Thom A, Jan 14 '20 at 16:07

Thom A · Accepted Answer · 2020-01-14T17:51:51.340

1

The "easiest" way to pivot data, in SQL Server and in my opinion, is by using a Cross Tab. Based on the sample data we have, this would mean that your query would look something like this:

SELECT idPeriodo,
       MAX(CASE codigo WHEN 7 THEN valor END) AS [7],
       MAX(CASE codigo WHEN 8 THEN valor END) AS [8],
       MAX(CASE codigo WHEN 9 THEN valor END) AS [9]
FROM dbo.YourTable
GROUP BY idPeriodo;

Seems you need a dynamic pivot, which would be like the below:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'SELECT idPeriodo,' + @CRLF +
           STUFF((SELECT N',' + @CRLF +
                         N'       MAX(CASE codigo WHEN ' + QUOTENAME(codigo,'''') + N' THEN valor END) AS ' + QUOTENAME(codigo)
                  FROM dbo.YourTable
                  GROUP BY codigo
                  ORDER BY codigo
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + @CRLF +
          N'FROM dbo.YourTable' + @CRLF +
          N'GROUP BY idPeriodo;';
--PRINT @SQL; --Your debugging friend.
EXEC sp_executesql @SQL;

edited Jan 14 '20 at 17:51

answered Jan 14 '20 at 16:09

Thom A

88,727
11
45
75

Thanks to answer. The problem is "codigo" is not a static table. So, codigo could has 1000 values. it is a dinamic table who always is growing up. so I can't use a CASE clause in my select query. I have executed the query using your suggestion and that works, but just for the example I provided. I need to do this with all codigos in the table – Allanh Jan 14 '20 at 16:19
So you need a dynamic Pivot? That information should have been in your question, but still, we can work with that. Give me 10. – Thom A Jan 14 '20 at 16:32
Yes, I think I need a pivot, but I'm not an expert in SQL, so I didn't know about pivot, I'm reading in this moment about that. I gave you 10 ;) – Allanh Jan 14 '20 at 16:37
I meant give me 10 minutes, @Allanh , that was 5 ;). Anyway, new answer done. – Thom A Jan 14 '20 at 16:39
You're a master. Really, I don't know what to say. Thanks so much. The more I learn the more ignorant I feel. Thanks so much. Just a simple correction of typographical error. You wrote twice MAX CASE( – Allanh Jan 14 '20 at 17:15
Oops, fixed @Allanh . – Thom A Jan 14 '20 at 17:52

score 0 · Answer 2 · answered Jan 14 '20 at 16:22

0

One option would be using pivot :

select idPeriodo, [7],[8],[9] 
  from tab
 pivot
 (
  max(valor) for codigo in ([7],[8],[9])
 ) t;

Demo

answered Jan 14 '20 at 16:22

Barbaros Özhan

59,113
10
31
55

Thanks Barbaros, but how can I build a pivot dinamically? I mean, 7,8 and 9 are values who are not static, I could have 1000 values or just one, it depends for the table who save this values. – Allanh Jan 14 '20 at 16:24
2

you're welcome. There's a famous [answer](https://stackoverflow.com/a/10404455/5841306) of Taryn for dynamic pivot @Allanh . – Barbaros Özhan Jan 14 '20 at 16:27

How to include empty values using pivot dinamically in a query to create relationship between column and row

2 Answers2