CODEWITHSUNDEEP
sqlsql-serverpivot
Gabriele Cozzolino
Gabriele Cozzolino

Reputation: 194

In Pivot join tables and return value only from one of them if it has values

I have this query (SQL fiddle at the bottom):

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @listaId as NVARCHAR(MAX)
SET @listaId = @selezione
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+  QUOTENAME(CONVERT(DATE,data,105)) FROM (SELECT DISTINCT  FORMAT ( data, 'd', 'it-IT' ) as data FROM PIANOLAVORO WHERE (data>=@dataDa and data<=@dataA)) AS [data]

SET @DynamicPivotQuery = ';WITH CTE AS(
SELECT v.data,t.cognome,s.CVDESCR FROM pianolavoro v
INNER JOIN orari s ON s.id = v.idorario
INNER JOIN dbstartprv.dbo.ANAGRAFICO_DATI t ON t.id = v.iddip
WHERE v.IDDIP in('+@listaId+')
)
SELECT COGNOME,'+@ColumnName+' FROM CTE
PIVOT (MAX(CVDESCR) FOR [data] IN('+@ColumnName+')) p
ORDER BY cognome ASC'
EXEC(@DynamicPivotQuery)

that gives me a result in this form:

       2019-03-01   2019-03-02   ...
jack       A             B
john       B             A

A and B are get from this join INNER JOIN orari s ON s.id = v.idorario and I would like to show other data instead of A, B only if in the same day something exists on another table. This is the join to be added in the above query join giustif g on t.id=g.iddip and g.idvoce=1001

Basically if in table pianolavoro on the 1st of march Jack has "A" but also he has a value on the same day in giustif table (say I get only id values = 1001), I want to display this:

       2019-03-01   2019-03-02   ...
jack     1001            B
john       B             A

SQL FIDDLE If you run the fiddle: the user Cozzolino has 2 resting days (1-2 june) and 5 working days (3-7 june) and on 6 and 7 of june he has a record in the table giustif. Now my query give me the result as in the fiddle, but I want it to be like:

            2019-06-01   2019-06-02   ...  2019-06-05  2019-06-06
Cozzolino     REST          REST      ...     WORK        1001

How can I achieve that? Thanks

Upvotes: 0

Views: 44

Answers (1)

Gabriele Cozzolino
Gabriele Cozzolino

Reputation: 194

Ok, I have to answer my own question:

coalesce(x.descr,s.CVDESCR) as cvdescr instead of s.CVDESCR and add 

left join giustif g on t.id=g.iddip and v.data=g.data
left join vocibase x on x.id=g.idvoce and idvoce=1001

Result query is:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @listaId as NVARCHAR(MAX)
SET @listaId = @selezione
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+  QUOTENAME(data) FROM (SELECT DISTINCT  FORMAT ( data, 'd', 'it-IT' ) as data FROM PIANOLAVORO WHERE (data>=@dataDa and data<=@dataA)) AS [data]

SET @DynamicPivotQuery = ';WITH CTE AS(
SELECT v.data,t.cognome,coalesce(x.descr,s.CVDESCR) as cvdescr FROM pianolavoro v
INNER JOIN orari s ON s.id = v.idorario
INNER JOIN dbstartprv.dbo.ANAGRAFICO_DATI t ON t.id = v.iddip
left join giustif g on t.id=g.iddip and v.data=g.data
left join vocibase x on x.id=g.idvoce and idvoce=1001
WHERE v.IDDIP in('+@listaId+')
)
SELECT COGNOME,'+@ColumnName+' FROM CTE
PIVOT (MAX(CVDESCR) FOR [data] IN('+@ColumnName+')) p
ORDER BY cognome ASC'
EXEC(@DynamicPivotQuery)

Upvotes: 1

Related Questions