Ion Ion - 8 days ago 6
SQL Question

evit duplicity in pivot table

I've created a T-SQL code to receive some data like this:

img1

But this cause duplicity of CODART and NOMBRE when the data is in different columns(COL1,COL2,COL3,COL5,....)

and I would like to appear all the data in ONE row, something like this:

img2

this is my code:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
SELECT *
FROM
(
SELECT sum(CUENTA) [cuenta], CODIGO,(select max(ar.nombre) from
CON.dbo.ARTICULOS ar where ar.codart=a.Codigo) as NOMBRE,
COL from LIBRO_COLUMNA l group by COL,CODIGO,
) AS j
PIVOT
(
SUM(cuenta) FOR COL IN('+@columns+')
) AS pivottable ORDER BY CODIGO;';
--PRINT @sql;
exec sp_executesql @sql


The estructure and some data of the table:

COL codigo nombre Cuenta

*--------------------------------------------------------------------

COL5 | PHOBA802 | TAPA FINAL D-UT 2,5/10 | 3624.00

COL 5 | PHOBA8021 | TAPA FINAL D-UT 16 | 159.00

COL 5 | PHOBA803 | TAPA FINAL D-UT 2,5/4 | 1950.00

COL 10 | PHOBA803 | | 447.00

COL 1 | PHOBA803 | TAPA UT 2,5-4 | 138.00


The problem here is that if want a pivot with COL I need to group by CODIGO and COL,and I donĀ“t know two to do it without grouping by COL.

Any suggestions?

DVT DVT
Answer

How about something like this?

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
SELECT
    subquery.*
    , (select max(ar.nombre) from 
CON.dbo.ARTICULOS ar where ar.codart=subquery.Codigo) as NOMBRE
FROM
(
SELECT *
FROM
(
  SELECT sum(CUENTA) [cuenta],
  COL from LIBRO_COLUMNA l group by COL,CODIGO,
) AS j
PIVOT
(
  SUM(cuenta) FOR COL IN('+@columns+')
) AS pivottable

) subquery ORDER BY subquery.CODIGO;
';
--PRINT @sql;
exec sp_executesql @sql

---- Added later---- More explanation on the why.

In the text T-SQL Fundamental and the one prepare for 70-461 exam, Itzik Ben-Gan explained the PIVOT.

SELECT <select list>
FROM (
    SELECT
        <grouping column>
        , <spreading column>
        , <aggregating column>
    ) PIVOT ( <aggregate function> (<aggregation column>)
        FOR <spreading column> IN (<spreading values>) ) as P;

From my understanding of his writing, by added in the subquery row, you added in a grouping column in there (think of the grouping as part of the ... GROUP BY grouping column, spreading column).

Hence, in this situation, I moved the subquery row out of the table being pivoted and hence remove the duplicate.

Comments