HELP!!!! dynamic pivot query w/multiple pivot columns

[lstieg]

Ok, pretty new to SQL but I have the following....

Tbl columns

client class
client name
username
useremail
rptnum
rptname
parametername
parametervalue

For each report the useremail/parametername can be multiple values so instead of reporting as rows I need them in seperate columns see attached.

I have the following sql using a pivot on email which works fine BUT... 2 issues....

One it is not letting me include a where clause in this query & 2 I need to pivot on more than just "name"

DECLARE @Names nvarchar(max)
SELECT @Names =
STUFF(
(
select distinct ',[' + v.useremail + ']'
from vw_clientemailedreportsresult v
inner join interval i
on v.intervalid = i.intervalid
for xml path('')
),
1,1,'')

DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select *
from
(
select distinct v.clientclass, v.clientname, v.reportnumber, v.reportname, i.intervalname, v.useremail
from vw_ClientEmailedReportsResult v
inner join interval i
on v.intervalid = i.intervalid
) as p
PIVOT (max(useremail) FOR useremail IN
(
' + @Names + '
)
) as pv
'

exec sp_executesql @SQL

The above works for just the emails... i really need the below to work for the emails and parameters.... Also would like to include the WHERE clause

DECLARE @Names nvarchar(max)
SELECT @Names =
STUFF(
(
select distinct ',[' + v.useremail + ']'
from vw_clientemailedreportsresult v
inner join interval i
on v.intervalid = i.intervalid
for xml path('')
),
1,1,'')

DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select *
from
(
SELECT rs.ReportSchdID, rs.BOEInstanceID, rs.ClientInstId, rs.Status, rs.StartTime, rs.EndTime, rs.ResultsMSG,
u.ClientClass, u.ClientName, u.UserName, u.UserEMail, r.ReportNumber, r.ReportName, iv.intervalname,
e.exportname, d.distributionname, rp.parametername, rsp.parametervalue
FROM dbo.Results AS rs
INNER JOIN dbo.ClientInstance AS i
ON rs.ClientInstId = i.ClientInstId
AND rs.ReportSchdID = i.ReportSchdID
INNER JOIN dbo.Batch AS b
ON i.BatchID = b.BatchID
INNER JOIN dbo.ReportSchd AS rr
ON rs.ReportSchdID = rr.ReportSchdID
INNER JOIN dbo.Users AS u
ON i.Client_Class = u.ClientClass
INNER JOIN dbo.Reports AS r
ON i.ReportID = r.ReportID
inner join interval iv
on rr.intervalid = iv.intervalid
inner join dbo.rptschdparameters rsp
on rs.reportschdid = rsp.reportschdid
and rs.clientinstid = rsp.clientinstid
inner join dbo.reportparameters rp
on rsp.reportid = rp.reportid
and rsp.reportparameterid = rp.reportparameterid
inner join dbo.exportformat e
on rr.exportformatid = e.exportformatid
inner join distributiontype d
on rr.distributiontypeid = d.distributiontypeid
WHERE (i.StartDateTime >= "05/08/2007")
AND (u.Active = "Y")
AND (u.UserEMail = ANY
(SELECT value
FROM dbo.fn_Split
((SELECT EmailTo
FROM dbo.EmailDistribution AS x
WHERE (ReportSchdID = i.ReportSchdID)
AND (ClientInstId = i.ClientInstId)), ";") AS s))
)as p
PIVOT (max(useremail) FOR useremail IN
(
' + @Names + '
)
) as pv
'
exec sp_executesql @SQL

Can anyone help??? This is an urgent request that I have to fulfill by end of day today.

 

[markros]

If you need dynamic pivot on multiple columns, then take a look at this blog post

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dynamic-pivot-on-multiple-columns

PluralSight Learning Library