PIVOT dynamically, Returned results from JOIN of two tables
Maybe I am missing something but you should be able to PIVOT the data but you will need to implement row_number() to help generate the columns.
The key will be to use a query similar to:
SELECT ONE.UserID,
ONE.Episode,
ONE.Value,
TWO.Details,
'Details'
+cast(row_number() over(partition by one.userid, one.episode
order by two.details) as varchar(10)) seq
FROM TABLE1 ONE
INNER JOIN TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
This will create a unique sequence for the new columns names, then you can apply the PIVOT:
select userid, episode,
value,
details1,
details2
from
(
SELECT ONE.UserID,
ONE.Episode,
ONE.Value,
TWO.Details,
'Details'
+cast(row_number() over(partition by one.userid, one.episode
order by two.details) as varchar(10)) seq
FROM TABLE1 ONE
INNER JOIN TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
) d
pivot
(
max(details)
for seq in (Details1, Details2)
) piv;
See SQL Fiddle with Demo. Then you can convert this to dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME('Details'+cast(seq as varchar(10)))
from
(
select
row_number() over(partition by one.userid, one.episode
order by two.details) seq
FROM TABLE1 ONE
INNER JOIN TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
) d
group by seq
order by seq
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT userid, episode, value, ' + @cols + '
from
(
SELECT ONE.UserID,
ONE.Episode,
ONE.Value,
TWO.Details,
''Details''
+cast(row_number() over(partition by one.userid, one.episode
order by two.details) as varchar(10)) seq
FROM TABLE1 ONE
INNER JOIN TABLE2 Two
ON ONE.UserID = TWO.UserID
AND ONE.Episode = TWO.Episode
) x
pivot
(
max(details)
for seq in (' + @cols + ')
) p '
execute sp_executesql @query;
See SQL Fiddle with Demo. Giving you the result:
| USERID | EPISODE | VALUE | DETAILS1 | DETAILS2 |
|--------|---------|-----------|-----------|-----------|
| 1 | 1 | VALUE 1-1 | Details 1 | Details 2 |
| 1 | 2 | VALUE 1-2 | Details 1 | Details 2 |
Join Two tables using Dynamic Pivot Query
You have to use dynamic query as below and you can test is by adding more FieldNames
CREATE TABLE #table1(EmpID INT,
Code VARCHAR(20),
Name VARCHAR(20),
Fld1 VARCHAR(20),
Fld2 VARCHAR(20),
Fld3 VARCHAR(20),
Fld4 VARCHAR(20))
INSERT INTO #table1 VALUES
(1, '1008M','ABC','temp1','temp2','temp3',NULL),
(2, '1039E','XYZ','temp1',NULL,NULL,null),
(3, '1040E','TYS',null,NULL,NULL,'temp6')
CREATE TABLE #table2(EmpID INT, FieldName VARCHAR(20), VALUE INT)
INSERT INTO #table2 VALUES
(1,'FH',1000),
(1,'FB',1220),
(2,'FHRA',3000),
(2,'FB',3000),
(3,'FB',3000)
DECLARE @col VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)
SELECT @col = COALESCE(@col + ', ','') + QUOTENAME(FieldName)
FROM #table2 GROUP BY FieldName
SELECT @col -- This gives: [FB], [FH], [FHRA]
-- Now setting this @col variable in the Dynamic SQL.
SET @sql = '
select EmpID, Code, Name,Fld1,Fld2,Fld3,Fld4, ' + @col + '
from (select a.EmpID, Code, Name,Fld1,Fld2,Fld3,Fld4, b.FieldName, b.value
from #table1 a
join #table2 b on a.empid=b.empid)p
PIVOT(MAX (VALUE) FOR FieldName IN ( ' + @col + ' )
) AS pvt
'
PRINT @sql
EXEC (@sql)
OUTPUT:
EmpID Code Name Fld1 Fld2 Fld3 Fld4 FB FH FHRA
1 1008M ABC temp1 temp2 temp3 NULL 1220 1000 NULL
2 1039E XYZ temp1 NULL NULL NULL 3000 NULL 3000
3 1040E TYS NULL NULL NULL temp6 3000 NULL NULL
Dynamically looping multiple tables to inner join clause
Since you are adding your static beginning string to the variable each time, you are getting your current results. Add another variable to store the moving target, and move the static bit out of the WHILE loop.
DECLARE @max int = 20
DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName VARCHAR(50)
DECLARE @id int = 1
Declare @Param1 varchar(20) ='p1', @param2 varchar(20) ='p2'
DECLARE @qry NVARCHAR(MAX)
DECLARE @SQL2 VARCHAR(MAX) = ' '
Declare @strcolumns nvarchar(max) = 'select c1, c2, c3, ...c30 from primarytable '
---------------
select @max = MAX(Id) from @Table
WHILE (@id <= @max)
BEGIN
SELECT @TableName = TableName FROM @Table WHERE Id = @id
SET @SQL = 'INNER JOIN '+ @TableName + ' ON p.' + @Param1 + ' = ' + @TableName + '.'+ @param1 + ' AND p.' + @Param2 + ' = ' + @TableName + '.'+ @param2 + ' '
SET @id = @id +1
SET @SQL2 = @SQL2 + @SQL
END
SET @strcolumns = @strcolumns + @SQL2
PRINT (@strcolumns)
How to join two dynamic pivot(table data after pivot) in SQL Server
Honestly, this should be something for your presentation layer not the SQL layer; especially as you want merged cells (a concept that does not exist in SQL).
I would personally switch over to conditional aggregation, rather than the restrictive PIVOT operator, and then do something like this:
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter nchar(3) = N',' + @CRLF;
WITH DateRanges AS(
SELECT DISTINCT
DATEADD(MONTH, DATEDIFF(MONTH,0,CreatedOn),0) AS StartDate,
DATEADD(MONTH, DATEDIFF(MONTH,0,CreatedOn)+1,0) AS EndDate
FROM dbo.TblDemoData)
SELECT @SQL = N'SELECT DD.AgentID,' + @CRLF +
STRING_AGG(N' COUNT(CASE WHEN DD.CreatedOn >= ' + QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''') + N' AND DD.CreatedOn < ' + QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''') + N' THEN 1 END) AS ' + QUOTENAME(CONCAT(YEAR(DR.StartDate),'-',DATENAME(MONTH,DR.StartDate),N'-Audits')) + N',' + @CRLF +
N' SUM(CASE WHEN DD.CreatedOn >= ' + QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''') + N' AND DD.CreatedOn < ' + QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''') + N' THEN AchievedScore* 1. ELSE 0 END) / ' + @CRLF +
N' SUM(CASE WHEN DD.CreatedOn >= ' + QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''') + N' AND DD.CreatedOn < ' + QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''') + N' THEN MaximumScore ELSE 0 END) AS ' + QUOTENAME(CONCAT(YEAR(DR.StartDate),'-',DATENAME(MONTH,DR.StartDate),N'-Score')),@Delimiter) WITHIN GROUP (ORDER BY DR.StartDate) + @CRLF +
N'FROM dbo.TblDemoData DD' + @CRLF +
N'GROUP BY DD.AgentId;'
FROM DateRanges DR;
--PRINT @SQL; Your debugging best friend
EXEC sys.sp_executesql @SQL;
Note this doesn't give the columns in the order you asked, however, the order of the columns is meaningless, and (again) should be controlled in your presentation layer.
How to perform a left join in my dynamic pivot query so it still returns rows
Maybe I'm misunderstanding your example, but I don't think the LEFT JOIN and PIVOT combo is your problem, I worked up a simple test case (forgive the shortened names) and it works fine
with cteCust as (
SELECT * FROM (VALUES ('Alice', 25), ('Bob', 22)
, ('Chris', 27), ('Doug', 21), ('Eve', 28)
) as Cust (Part, Age)
), cteTempCount as (
SELECT *
FROM (VALUES
('Alice', 'US', 5), ('Alice', 'EU', 2), ('Alice', 'GB', 4)
, ('Chris', 'GB', 3), ('Chris', 'JP', 6)
, ('Doug', 'JP', 7), ('Doug', 'EU', 4), ('Doug', 'EU', 9)
, ('Eve', 'US', 1), ('Eve', 'GB', 2), ('Eve', 'JP', 5)
) as TC(Part, Regn, RCount)
)
SELECT Part, Age, US, EU
FROM (SELECT u.Part, u.age, Regn, RCount FROM cteCust as U
LEFT OUTER JOIN cteTempCount as TC on U.Part = TC.Part) as X
PIVOT (SUM(RCount) FOR Regn in (US, EU)) as P;
gives results
Part Age US EU
Alice 25 5 2
Bob 22 NULL NULL
Chris 27 NULL NULL
Doug 21 NULL 13
Eve 28 1 NULL
which is giving a results row even for users with no matches in the desired regions (Chris) or no matches in TempCount whatsoever (Bob).
Is it possible some of the RegionNames aren't making good column names?
Or maybe whatever is consuming this output is having trouble with NULL values?
Maybe @cols is malformed?
Pivot while changing data types (dynamically)
If you must convert the datatype (could really be a presentation layer thing), then a dynamic conditional aggregation should do the trick.
Example
Declare @SQL nvarchar(max) ='
Select U.*' +
(
Select concat(',',quotename(fullname),'=max(case when attributeid=',id,' then try_convert(',type,',value) end)')
From customattributes
For XML Path ('')
)+'
From users U
Join customattributesvalues V on U.ID=V.userid
Group By U.ID
,U.FirstName
,U.LastName
,U.active
,U.datecreated
,U.username
,U.email
'
--print @SQL
Exec(@SQL)
Results
The Generated SQL Looks Like This
Select U.*
,[Hire Date]=max(case when attributeid=1 then try_convert(date,value) end)
,[Employee ID]=max(case when attributeid=2 then try_convert(int,value) end)
,[Supervisor]=max(case when attributeid=3 then try_convert(nvarchar(50),value) end)
,[Assigned Ship]=max(case when attributeid=4 then try_convert(nvarchar(50),value) end)
,[Job Title]=max(case when attributeid=5 then try_convert(nvarchar(50),value) end)
From #users U
Join #customattributesvalues V on U.ID=V.userid
Group By U.ID
,U.FirstName
,U.LastName
,U.active
,U.datecreated
,U.username
,U.email
Dynamic pivot table with multiple columns in sql server
Please use this (If you are getting Collation issue, please change all the 3 INT datatypes):
STATIC code:
SELECT HEADER, [A_1],[B_2],[C_3],[D_4],[E_5],[F_6]
FROM
(SELECT DECK,HEADER, VALUE FROM REPORT
UNPIVOT
(
VALUE FOR HEADER IN ([JIB_IN],[REV],[REV_INSIGHT],[JIB_OUT],[CREATION])
) UNPIV
) SRC
PIVOT
(
SUM(VALUE)
FOR DECK IN ([A_1],[B_2],[C_3],[D_4],[E_5],[F_6])
) PIV
Using Dynamic SQL:
DECLARE @COLSUNPIVOT AS NVARCHAR(MAX),
@QUERY AS NVARCHAR(MAX),
@COLSPIVOT AS NVARCHAR(MAX)
SELECT @COLSUNPIVOT = STUFF((SELECT ','+QUOTENAME(C.NAME)
FROM SYS.COLUMNS AS C
WHERE C.OBJECT_ID = OBJECT_ID('REPORT') AND C.NAME <> 'DECK'
FOR XML PATH('')), 1, 1, '')
SELECT @COLSPIVOT = STUFF((SELECT ',' + QUOTENAME(DECK)
FROM REPORT T FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
SET @QUERY
= 'SELECT HEADER, '+@COLSPIVOT+'
FROM
(
SELECT DECK,HEADER,VALUE FROM REPORT
UNPIVOT
(
VALUE FOR HEADER IN ('+@COLSUNPIVOT+')
) UNPIV
) SRC
PIVOT
(
SUM(VALUE)
FOR DECK IN ('+@COLSPIVOT+')
) PIV'
EXEC(@QUERY)
Related Topics
Sqlite Inner Join - Update Using Values from Another Table
Setting Up Rails to Work with SQLserver
Writing a Subquery Using Zend Db
Referencing Current Row in Filter Clause of Window Function
Simple SQL Select from 2 Tables (What Is a Join)
What Did Mongodb Not Being Acid Compliant Before V4 Really Mean
Efficient Way of Getting @@Rowcount from a Query Using Row_Number
Import Excel Data into Postgresql 9.3
SQL Server Using Wildcard Within In
How to Skip the First N Rows in SQL Query
Oracle Convert Timestamp with Timezone to Date
Rails Union Hack, How to Pull Two Different Queries Together
Adding 'Go' Statements to Entity Framework Migrations
Query to Order by the Number of Rows Returned from Another Select
How to Select Records from Last 24 Hours Using SQL