Last post May 22, 2018 01:17 AM by Deepak Panchal
May 21, 2018 06:34 AM|baoshenglishanghai|LINK
I have been stuck on this for a long time now, I hope that someone can help!
Please refer to this SQLFiddle for the table structure:
SQL server 2008 used.
I need to create a raw data report from several tables like this one. Below template is the only way I have found so far that could allow the data to be usable for further analysis after export. Please note that there are 2 different types: 'IN' and 'EX'
that I would need to display as in the below example. I would then also need to rename the Type column to something like 'Internal Check', for the 'IN' Type.
Link to picture on tinypic.com
(Sorry, but I was unable to upload a picture here, seems the functionality does not exist or is broken. Insert button did not work for me).
I hope the idea is clear, if not please let me know, I can update this post and / or the related fiddle.
In addition to above request, I would also like to know how I could incorporate the result to another query, basically doing a join on SN.
Many thanks in advance!
May 22, 2018 01:17 AM|Deepak Panchal|LINK
The easiest way I find is as an example below.
It is not the exact but it can fulfil your requirement.
SELECT SN, Type,
STUFF((SELECT DISTINCT ' , ' + problemLocation + ' , ' + problemCode + ' , ' + remarks
FROM TestResults b
WHERE b.SN = a.SN
FOR XML PATH('')), 1, 2, '') as problemLocation_problemCode_remarks
FROM TestResults a
GROUP BY SN,Type