Using UNPIVOT in constructed String Query Dynamic

Question

I am building a query where I will need a UNPIVOT on dynamic columns. (abcd are example string name)

data1 data2 com   fr random
  1     2    a    d    sq
  3     4    b    a    fd

UNPIVOT like so :

data1 data2 Name Website random
  1     2    a     com     sq
  1     2    d     fr      sq
  3     4    b     com     fd
  3     4    a      fr     fd

The matter here is for building my First table I use dynamic SQL (@QueryFinal) because of the column. Here is my UNPIVOT dynamic Query

'SELECT data1, data2, Name, Website
FROM '+@QueryFinal+'
UNPIVOT (
         Name FOR Website in ('+@WebsiteCol+')
         ) f;'

In my @QueryFinal I have a WHERE .... ORDER BY, it seems like the UNPIVOT can't handle it. When I delete the WHERE and ORDER BY clause I get the error :

Incorrect syntax near the keyword 'UNPIVOT'.

`PRINT` is your friend. Have a look at the output of `PRINT @YourDynamicSQLVariable;` and try to debug it; if you're having trouble post the output in your question. — Thom A, Jul 25 '18 at 09:31

Rigerta · Accepted Answer · 2018-07-25T09:52:44.293

2

Try the following dynamic-pivot:

--drop table if exists unpivottest
create table unpivotTest (data1 int, data2 int, com char(1), fr char(1))

insert into unpivotTest 
select 1, 2, 'a' , 'd' union all
select 3, 4, 'b', 'a' 

select * from unpivotTest

declare @colsunpivot as nvarchar(max),
   @query  as nvarchar(max)

select @colsunpivot = stuff((select ','+ quotename(c.name)
                             from sys.columns c
                             where c.object_id = object_id('dbo.unpivottest') and c.name not like '%data%'
                             for xml path('')), 1, 1, '')

set @query 
          = 'select data1, data2, name, website
             from unpivottest
             -- you cannot do the ordering here
             unpivot
             (
                name
                for website in ('+ @colsunpivot +')
             ) u
            where data1 = 1 -- here you can use your where clause
            order by data1' -- here you can do the ordering by any col
--print @query
exec sp_executesql @query;

Check a working demo here.

edited Jul 25 '18 at 09:52

answered Jul 25 '18 at 09:31

Rigerta

3,959
15
26

The '@colsunpivot' represent ALL the columns of my First table ? Because I have other columns than 'com' and 'fr' which I don't want to UNPIVOT, my '@colsunpivot' represent all the Website columns I want to unpivot – Benoît Jul 25 '18 at 09:34
Not all of them, all except the ones with `data` in the name. Notice the condition `and c.name not like '%data%'` inside the `stuff` function. That is so that we only take into account the `com` & `fr` cols when un-pivot-ing. If you have more such columns, for ex. us, en etc they will also be included dynamically. – Rigerta Jul 25 '18 at 09:36
i see but I don't see any difference with what I've done... The 'unpivottest' query has a WHERE and ORDER BY clauses. My '@WebsiteCol' represent all the columns I want to UNPIVOT. '@WebsiteCol' = fr, com, ru, de – Benoît Jul 25 '18 at 09:38
You should post the full script for your query. Maybe we can find the issue by looking at all of it. – Rigerta Jul 25 '18 at 09:41
I think you are doing the order by in the wrong place. You can order the result set after unpivoting it. I will update the answer. – Rigerta Jul 25 '18 at 09:46
Demiri Thanks I have made big progress ! I'm almost done :) I'll post the answer soon – Benoît Jul 25 '18 at 09:47
Without the WHERE and ORDER BY clauses my Query with UNPIVOT works ! Now I need to find a way to put them... – Benoît Jul 25 '18 at 09:49

score 0 · Answer 2 · answered Feb 04 '19 at 08:47

Even if it isn't the same column name as in the example here is the final Query built thanks for the help. I put the WHERE clause in the first SELECT and the ORDER BY in the UNPIVOT

DECLARE @QueryFinal VARCHAR(max) = @Query + @QueryBis  + '
from #CALENDAR_FINAL temp
LEFT JOIN #BURSTS b on b.bur_id = temp.bur_id
LEFT JOIN digital_calendar_status dcs ON dcs.dcs_date = temp.[Date] AND dcs.dif_id = '+convert(varchar(2),@FormatId)+'
LEFT JOIN digital_calendar_event dce ON dce.dce_date = temp.[Date]
WHERE '+@ConditionConflict+@ConditionIcon+@ConditionDate

DECLARE @Pivot VARCHAR(2000)='
SELECT 
DateStr, f.Conflict, f.dcs_id, f.Status, f.Website, f.Advertiser,  f.Comment, f.EventName, f.EventIcone
FROM ('+@QueryFinal+') AS a
UNPIVOT
(
Advertiser
FOR Website IN ('+@WebsiteCol+')
) f
ORDER BY Date;
'

Using UNPIVOT in constructed String Query Dynamic

2 Answers2