SQL for dynamic mappings

[Danpeking]

Hi, I have a problem generating SQL to return a single row for a client. My table structure is the following... Clients - ClientID, ClientName Statuses - StatusID, StatusDescription, ClientID StatusMappings - StatusID, ClientID The statuses can be different depending on the client, so for example, clients table: 1, Barclays 2, Halifax Statuses table: 1, Draft Letter, 1 2, Complete, 1 3, Draft Letter, 2 4, Awaiting Response, 2 5, Complete, 2 StatusMappings table: 1, 1 (Draft Letter for Barclays) 3, 2 (Draft Letter for Halifax) 4, 2 (Awaiting Response for Halifax) How I want to display the data in a report (based on client selected), for example : Client | Draft Letter | Awaiting Response | Complete | ------------------------------------------------------------- Halifax Y Y I'm not sure how I would best write the SQL for this. Also I would like to produce SSRS reports based on it. Can anybody please advise? Thanks

[CHill60]

I'm not sure about the example you are presenting ... Are the following statements true? - The Statuses table shows the possible statuses for each Client? - The StatusMapping table shows the actual statuses for each client. If that is the case then with the data you presented you cannot possibly get the results because for Client "Halifax" Status 'Draft Letter' is Id 3 and there is nothing on the StatusMappings table for that Id. Similarly 'Awaiting Response' would be Id 4.

[CHill60]

Assuming you get the data sorted out then something like the following will work

SELECT * FROM
(
SELECT C.ClientID, ClientName, StatusDescription,
CASE WHEN SM.StatusID IS NOT NULL THEN 'Y' ELSE 'N' END AS Actual
FROM @Clients C
LEFT OUTER JOIN @Statuses S ON C.ClientID = S.ClientID
LEFT OUTER JOIN @StatusMappings SM on SM.StatusID = S.StatusID
) qry
PIVOT
(
MAX(Actual) FOR StatusDescription in ([Draft Letter],[Awaiting Response],[Complete])
) pvt
WHERE ClientName = 'Barclays'

If you want to use it in an SSRS report then put the query into a Stored Procedure, passing the Client name or id as a parameter. If the Status Descriptions are not standard or consistent (i.e. not the ones listed) then you will have to use some dynamic sql. There are articles here on CodeProject on all the topics above

[Danpeking]

You're quite right sorry, I've amended the data. Thanks!

[Danpeking]

Thanks CHill60, yes I will need to use dynamic SQL. I've seen various articles using cursors, XML, etc. I just wondered which was the best approach to take.

[CHill60]

Cursor is a definite "No!" :laugh: (See my article Processing Loops in SQL Server[^] ) XML is a definite possibility, but the code I've shown using PIVOT is quite compact. There is an example of generating dynamic sql for a pivot in the article.

[Danpeking]

Wow, looks a great article. Thanks, will take a look at this and reply once I have.

[Danpeking]

I achieved this in the end by dynamically creating strings to pass in to a SQL statement I was building up. I used MAX and Case statements to achieve this. I finished it an EXEC (@MyString). Thanks for the advice!