Menu
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)
936
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
