Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dynamic pivot in SQL Server

I have problem with output from pivot. I need it in my desired form to coalesence tables.

My result:

us_id   Phone           Mail
1   555121313   NULL
1   NULL            [email protected]
2   14124124    NULL
2   NULL            [email protected]

Desired output:

us_id   Phone   Mail
1   555121313   [email protected]
2   14124124    [email protected]

Script for test:

create table #user (us_id int, us_login varchar(255))
INSERT INTO #user VALUES (1, 'LoginOne')
INSERT INTO #user VALUES (2, 'LoginTwo')

create table #atr_type (at_id int, at_name varchar(255))
insert into #atr_type values (1,'Phone');
insert into #atr_type values (2,'Mail')

create table #atr (atr_id int, atr_us_id int, atr_at_id int, atr_value varchar(255))
insert into #atr values(1,1,1,'555121313')
insert into #atr values(2,1,2,'[email protected]')
insert into #atr values(3,2,1,'14124124')
insert into #atr values(4,2,2,'[email protected]')

My code for dynamic pivot:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N',' + QUOTENAME(at_name)
  FROM (  SELECT   at_name
FROM       #atr_type) AS x;

select @columns

declare @cmd varchar(max)
set @cmd = '
 SELECT us_id'+@columns+'
 FROM         #user inner join #atr on atr_us_id = us_id
inner join #atr_type on at_id = atr_at_id
PIVOT (
MIN(atr_value) FOR at_name IN
 ('

set @columns = RIGHT(@columns, LEN(@columns)-1)
set @cmd += @columns + ')
) AS PT'
exec (@cmd)
like image 936
VoonArt Avatar asked May 26 '13 13:05

VoonArt


People also ask

What is dynamic PIVOT in SQL Server?

Dynamic pivot query will fetch a value for column names from table and creates a dynamic columns name list for pivot table. So whatever the values for column name will be in a table are considered as a columns for pivot table. Lets see how a dynamic pivot query is used to display a dynamic columns in pivot table.

What is dynamic PIVOT?

A dynamic range will automatically expand or contract, if new columns or rows of data are added, or data is removed. You can base a pivot table on the dynamic range. Then, when you refresh the pivot table, it will include all of the data in the range, even if new rows or columns have been added.

How do I PIVOT two columns in SQL Server?

You gotta change the name of columns for next Pivot Statement. You can use aggregate of pv3 to sum and group by the column you need. The key point here is that you create new category values by appending 1 or 2 to the end. Without doing this, the pivot query won't work properly.


1 Answers

Try this:

DECLARE
    @columns NVARCHAR(1000) = ''
  , @columns2 NVARCHAR(1000) = ''
  , @sql NVARCHAR(MAX)

SELECT
    @columns += N', [' + at_name + ']'
  , @columns2 += N', [' + at_name + '] = MAX([' + at_name + '])'
FROM #atr_type

SET @sql = '
 SELECT us_id' + @columns2 + '
 FROM #user inner join #atr on atr_us_id = us_id
join #atr_type on at_id = atr_at_id
PIVOT (
MIN(atr_value) FOR at_name IN
 (' + STUFF(@columns, 1,1,'') + ')
) AS PT
Group by us_id'

EXEC sp_executesql @sql
like image 70
Jasmina Shevchenko Avatar answered Sep 29 '22 15:09

Jasmina Shevchenko