July 16, 2011 at 1:54 am
Hi Team,
My front end form saves the data in the data base as follows
PNO Injury Theft Selfharm
ABC 1 1 0
XYZ 1 0 0
GHY 0 0 0
I want the o/p as follows:
PNO Incident Type
ABC Injury
ABC Theft
XYZ Injury
GHY No Incidents.
So, in a nut shell the incident types ( Injury, Injury with Hosp, Selfharm) stores in the database as in 1 and 0.
I need to run a report and the o/p should display as above. Patient ABC, has two incidents, so in the o/p report I need to have two records,with the incident type as the name of the incident. If no incident are occurred for that patient, it should appear as No incidents for that patient number.
How can I achieve this in SQl server 2005?
Thanks.
July 16, 2011 at 2:56 am
Would something like the following help you to get started?
DECLARE @tbl TABLE
(
PNO CHAR(3),Injury INT,Theft INT, Selfharm INT
)
INSERT INTO @tbl
SELECT 'ABC', 1, 1, 0 UNION ALL
SELECT 'XYZ', 1, 0, 0 UNION ALL
SELECT 'GHY', 0, 0, 0
SELECT PNO,Incident,valid
FROM
(SELECT * FROM @tbl
) p
UNPIVOT
( valid FOR Incident IN
(Injury, Theft, Selfharm)
)AS unpvt
July 16, 2011 at 7:11 pm
Hi Lutz,
Thanks for your reply.
What happens, if I want to make my select statement dynamic.
My actual request from client is to get all the incidents that are raised as a daily report looking back to the previous days incidents.
so, we don't know the number of records. Is that mean, do I have to run the select statement against the Incident table to get all the necessary columns, export to an excel and then use your logic to create an temp table and insert the exported information into that temp table.
Is there any way to get it done, by changing the logic you posted to make it more dynamic.
BTW, the data I posted is a sample data, means I have more columns such as incidentdate, Incidentnumber etc, which I have to use.
Thanks,
Kris.
July 17, 2011 at 2:38 am
kish1234 (7/16/2011)
Hi Lutz,Thanks for your reply.
What happens, if I want to make my select statement dynamic.
My actual request from client is to get all the incidents that are raised as a daily report looking back to the previous days incidents.
so, we don't know the number of records. Is that mean, do I have to run the select statement against the Incident table to get all the necessary columns, export to an excel and then use your logic to create an temp table and insert the exported information into that temp table.
Is there any way to get it done, by changing the logic you posted to make it more dynamic.
BTW, the data I posted is a sample data, means I have more columns such as incidentdate, Incidentnumber etc, which I have to use.
Thanks,
Kris.
Please post some sample (e.g. two samples to demonstrate the "dynamic" requirement) together with your expected result. It would be hoghly appreciated if you could use the same format that I used to provide the data (table def and INSERT statements). Also, please post your exptecte result based on those sample data.
I don't understand what you want to use EXCEL for :sick:
I used the table format exactly as you posted it, just in a ready to use format.
And no, there won't be any reason to use an office calculation sheet to perform database work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply