Solved

Query syntax

Posted on 2013-11-03
13
172 Views
Last Modified: 2013-11-09
MS SQL Server 2008.

I have two tables which are joined on the column tra_id.

There are many rows in the PatientInjuries table for each row in the Trauma table.

[Trauma](
  [tra_id] [int] IDENTITY(1,1) NOT NULL,
  [pat_id] [int] NOT NULL,
  ......
  ......

)

[PatientInjuries](
  [pij_id] [int] IDENTITY(1,1) NOT NULL,
  [tra_id] [int] NOT NULL,
  [icd_ICD] [nvarchar](10) NOT NULL,
) 

Open in new window



I am writing an export where i need one row per tra_id.  

So i need to produce all the Trauma columns (easy Trauma.*) plus all the icd_ICD columns for each tra_id.

My problem is i dont know how to join the two tables so that get the many rows in the ProjectInjuries table to appear as columns in the output.

For example:

With Trauma as
tra_id    pat_id 
10          18929

and 

PatientInjuries as
tra_id    icd_ICD
10         ABCD
10         ABC1
10         FRDA

Open in new window

i need the following:

10, 18929, ABCD, ABC1, FRDA

There are maximum 50 rows in the PatientInjuries table per row in the Trauma table-

Thanks
0
Comment
Question by:soozh
  • 6
  • 5
  • 2
13 Comments
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
try this

select tra_id, pat_id
      STUFF((SELECT ',' + icd_ICD
               FROM PatientInjuries A
               WHERE
               A.tra_id = B.tra_id
               FOR XML PATH('')
              ), 1, 1, '' )  AS [icd_ICD]
from Trauma B
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
Need to fix the missing comma
select A.tra_id, A.pat_id, 
	STUFF((SELECT ',' + icd_ICD
           FROM PatientInjuries B
           WHERE  A.tra_id = B.tra_id
               FOR XML PATH('')
              ), 1, 1, '' )  AS [icd_ICD]
from Trauma A

Open in new window

0
 

Author Comment

by:soozh
Comment Utility
Hello,

Thanks for the solution.  It is a good idea. I had never thought of return the icd codes in one column.  

However this may be a problem as the routine expects the codes to be in thier own columns.

I'll check it out though!
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>> how to join ...tables so that ... many rows .. appear as columns in the output.
there isn't any "join" type that achieves this, in fact the opposite effect is really achieved by joins in that details in trauma get repeated for each row in PatientInjuries.

So you are needing something above & beyond a join to "transpose" rows into columns, such as the "for xml path" approach suggested by soozh.

However you have added that you need information "in their own columns" and you stated at the outset there is a maximum of 50 rows.

A rather old-fashioned way to do this - but still efficient - is to use case expressions. However we can't do this without knowing which row (of the allowed 50) information belongs to. So it's a 2 step process:
1. give the PatientInjuries rows a row number
2. "transpose" the columns using case expressions
SELECT
        det.tra_id
      , det.pat_id
      , case when det.rn = 1  then det.icd_ICD else null end as icd_ICD_01
      , case when det.rn = 2  then det.icd_ICD else null end as icd_ICD_02
      , case when det.rn = 3  then det.icd_ICD else null end as icd_ICD_03
-- repeat as needed

      , case when det.rn = 50 then det.icd_ICD else null end as icd_ICD_50
FROM (
        SELECT
                t.tra_id
              , t.pat_id
              , pi.icd_ICD
              , row_number() over (partition by t.tra_id /* restarts at 1 by t.tra_id */
                                   order by (select 1) /* sequnce on the numbers is controlled here, there may be some other field(s) to use for this? */
                                  ) as rn
        FROM trauma AS t
                INNER JOIN PatientInjuries AS pi
                        ON t.tra_id = pi.tra_id
        ) AS det
WHERE det.rn <= 50 -- e.g. only: optional
GROUP BY
        t.tra_id
;

Open in new window

Now I know this may seem like lots of sql code when you expand it out for 50 rows - but it will work and place data into the wanted columns. Additionally if there are extras fields in PatientInjuries  you want as column then just repeat the case expressions.

There is another alternative, which is to use the "pivot" feature but to be honest I'd rather use case expressions for this.

{+edit} included the where clause example
0
 

Author Comment

by:soozh
Comment Utility
hello,

Thanks for the solution.  It looks good however there is a problem with the Group by clause.

I wonder if there is an extra set of brackets missing?
0
 

Author Comment

by:soozh
Comment Utility
Now i have come so far. See below.  This gives me the icd columns as needed.  

Two questions:

1) Does it look ok?

2) What is the best way to include all the other columns in the Trauma table?


select  
      tra_id
      , max(pat_id) as pat_id
      , max(icd_ICD_01) as icd_ICD_01
      , max(icd_ICD_02) as icd_ICD_02
      , max(icd_ICD_03) as icd_ICD_03
      , max(icd_ICD_04) as icd_ICD_04
      , max(icd_ICD_05) as icd_ICD_05
      , max(icd_ICD_06) as icd_ICD_06
      , max(icd_ICD_07) as icd_ICD_07
      , max(icd_ICD_08) as icd_ICD_08
      , max(icd_ICD_09) as icd_ICD_09
      , max(icd_ICD_10) as icd_ICD_10

      , max(icd_ICD_11) as icd_ICD_11
      , max(icd_ICD_12) as icd_ICD_12
      , max(icd_ICD_13) as icd_ICD_13
      , max(icd_ICD_14) as icd_ICD_14
      , max(icd_ICD_15) as icd_ICD_15
      , max(icd_ICD_16) as icd_ICD_16
      , max(icd_ICD_17) as icd_ICD_17
      , max(icd_ICD_18) as icd_ICD_18
      , max(icd_ICD_19) as icd_ICD_19
      , max(icd_ICD_20) as icd_ICD_20

      , max(icd_ICD_21) as icd_ICD_21
      , max(icd_ICD_22) as icd_ICD_22
      , max(icd_ICD_23) as icd_ICD_23
      , max(icd_ICD_24) as icd_ICD_24
      , max(icd_ICD_25) as icd_ICD_25
      , max(icd_ICD_26) as icd_ICD_26
      , max(icd_ICD_27) as icd_ICD_27
      , max(icd_ICD_28) as icd_ICD_28
      , max(icd_ICD_29) as icd_ICD_29
      , max(icd_ICD_30) as icd_ICD_30

      , max(icd_ICD_31) as icd_ICD_31
      , max(icd_ICD_32) as icd_ICD_32
      , max(icd_ICD_33) as icd_ICD_33
      , max(icd_ICD_34) as icd_ICD_34
      , max(icd_ICD_35) as icd_ICD_35
      , max(icd_ICD_36) as icd_ICD_36
      , max(icd_ICD_37) as icd_ICD_37
      , max(icd_ICD_38) as icd_ICD_38
      , max(icd_ICD_39) as icd_ICD_39
      , max(icd_ICD_40) as icd_ICD_40

      , max(icd_ICD_41) as icd_ICD_41
      , max(icd_ICD_42) as icd_ICD_42
      , max(icd_ICD_43) as icd_ICD_43
      , max(icd_ICD_44) as icd_ICD_44
      , max(icd_ICD_45) as icd_ICD_45
      , max(icd_ICD_46) as icd_ICD_46
      , max(icd_ICD_47) as icd_ICD_47
      , max(icd_ICD_48) as icd_ICD_48
      , max(icd_ICD_49) as icd_ICD_49
      , max(icd_ICD_50) as icd_ICD_50

  

from(
SELECT
        det.tra_id
      , det.pat_id
      , case when det.rn = 1  then det.icd_ICD else null end as icd_ICD_01
      , case when det.rn = 2  then det.icd_ICD else null end as icd_ICD_02
      , case when det.rn = 3  then det.icd_ICD else null end as icd_ICD_03
      , case when det.rn = 4  then det.icd_ICD else null end as icd_ICD_04
      , case when det.rn = 5  then det.icd_ICD else null end as icd_ICD_05
      , case when det.rn = 6  then det.icd_ICD else null end as icd_ICD_06
      , case when det.rn = 7  then det.icd_ICD else null end as icd_ICD_07
      , case when det.rn = 8  then det.icd_ICD else null end as icd_ICD_08
      , case when det.rn = 9  then det.icd_ICD else null end as icd_ICD_09
      , case when det.rn = 10  then det.icd_ICD else null end as icd_ICD_10

      , case when det.rn = 11  then det.icd_ICD else null end as icd_ICD_11
      , case when det.rn = 12  then det.icd_ICD else null end as icd_ICD_12
      , case when det.rn = 13  then det.icd_ICD else null end as icd_ICD_13
      , case when det.rn = 14  then det.icd_ICD else null end as icd_ICD_14
      , case when det.rn = 15  then det.icd_ICD else null end as icd_ICD_15
      , case when det.rn = 16  then det.icd_ICD else null end as icd_ICD_16
      , case when det.rn = 17  then det.icd_ICD else null end as icd_ICD_17
      , case when det.rn = 18  then det.icd_ICD else null end as icd_ICD_18
      , case when det.rn = 19  then det.icd_ICD else null end as icd_ICD_19
      , case when det.rn = 20  then det.icd_ICD else null end as icd_ICD_20

      , case when det.rn = 21  then det.icd_ICD else null end as icd_ICD_21
      , case when det.rn = 22  then det.icd_ICD else null end as icd_ICD_22
      , case when det.rn = 23  then det.icd_ICD else null end as icd_ICD_23
      , case when det.rn = 24  then det.icd_ICD else null end as icd_ICD_24
      , case when det.rn = 25  then det.icd_ICD else null end as icd_ICD_25
      , case when det.rn = 26  then det.icd_ICD else null end as icd_ICD_26
      , case when det.rn = 27  then det.icd_ICD else null end as icd_ICD_27
      , case when det.rn = 28  then det.icd_ICD else null end as icd_ICD_28
      , case when det.rn = 29  then det.icd_ICD else null end as icd_ICD_29
      , case when det.rn = 30  then det.icd_ICD else null end as icd_ICD_30

      , case when det.rn = 31  then det.icd_ICD else null end as icd_ICD_31
      , case when det.rn = 32  then det.icd_ICD else null end as icd_ICD_32
      , case when det.rn = 33  then det.icd_ICD else null end as icd_ICD_33
      , case when det.rn = 34  then det.icd_ICD else null end as icd_ICD_34
      , case when det.rn = 35  then det.icd_ICD else null end as icd_ICD_35
      , case when det.rn = 36  then det.icd_ICD else null end as icd_ICD_36
      , case when det.rn = 37  then det.icd_ICD else null end as icd_ICD_37
      , case when det.rn = 38  then det.icd_ICD else null end as icd_ICD_38
      , case when det.rn = 39  then det.icd_ICD else null end as icd_ICD_39
      , case when det.rn = 40  then det.icd_ICD else null end as icd_ICD_40

      , case when det.rn = 41  then det.icd_ICD else null end as icd_ICD_41
      , case when det.rn = 42  then det.icd_ICD else null end as icd_ICD_42
      , case when det.rn = 43  then det.icd_ICD else null end as icd_ICD_43
      , case when det.rn = 44  then det.icd_ICD else null end as icd_ICD_44
      , case when det.rn = 45  then det.icd_ICD else null end as icd_ICD_45
      , case when det.rn = 46  then det.icd_ICD else null end as icd_ICD_46
      , case when det.rn = 47  then det.icd_ICD else null end as icd_ICD_47
      , case when det.rn = 48  then det.icd_ICD else null end as icd_ICD_48
      , case when det.rn = 49  then det.icd_ICD else null end as icd_ICD_49
      , case when det.rn = 50  then det.icd_ICD else null end as icd_ICD_50

 
-- repeat as needed

 
FROM (
        SELECT
                t.tra_id
              , t.pat_id
              , pi.icd_ICD
              , row_number() over (partition by t.tra_id /* restarts at 1 by t.tra_id */
                                   order by (select 1) /* sequnce on the numbers is controlled here, there may be some other field(s) to use for this? */
                                  ) as rn
        FROM trauma AS t
                INNER JOIN PatientInjuries AS pi
                        ON t.tra_id = pi.tra_id
        ) AS det
WHERE det.rn <= 50 -- e.g. only: optional
) x


 
group by x.tra_id

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
It was my omission, sorry, I forgot the MAX() function.

You can do the max() and the case together instead of nesting it, like this:

SELECT
        det.tra_id
      , det.pat_id
      , MAX( case when det.rn = 1  then det.icd_ICD else null end ) as icd_ICD_01

But you seem to have the concept.
0
 

Author Comment

by:soozh
Comment Utility
How do i add the other columns from the Trauma table?

 Shall i just do an inner join to Trauma on tra_id?

 Or do i have to include them in the det table and use max on each column?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
>>How do i add the other columns from the Trauma table?
Extra fields from the trauma table don't have to be MAX()'ed, here's how I would do it:
SELECT
        det.tra_id
      , det.pat_id
      -- add the new det.% columns here
      , MAX( case when det.rn = 1  then det.icd_ICD else null end ) as icd_ICD_01
      , MAX( case when det.rn = 2  then det.icd_ICD else null end ) as icd_ICD_02
      -- repeat as needed
      
      , MAX( case when det.rn = 50 then det.icd_ICD else null end ) as icd_ICD_50
FROM (
        SELECT
                t.tra_id
              , t.pat_id
              -- add more traumas table fields here
              
              , pi.icd_ICD
              , row_number() over (partition by t.tra_id /* restarts at 1 by t.tra_id */
                                   order by (select 1) /* sequnce on the numbers is controlled here, there may be some other field(s) to use for this? */
                                  ) as rn
        FROM trauma AS t
                INNER JOIN PatientInjuries AS pi
                        ON t.tra_id = pi.tra_id
        ) AS det
WHERE det.rn <= 50 -- e.g. only: optional
GROUP BY 
        det.tra_id
      , det.pat_id
      -- adjust for more traumas table fields here

Open in new window

0
 

Author Comment

by:soozh
Comment Utility
ok so either i "max" them or include them in the Group By statement.

What do you recommend if there are about 50 extra columns!?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>ok so either i "max" them or include them in the Group By statement.
exactly
those items that will be "one-to-one" to the required rows form the group by, otherwise you need an aggregate function [like max() min() etc]

for another 50 columns, it's another 50 case expressions I'm afraid
but once you get the first one worked out, it's easy to copy/paste as new rows, then edit each

I laid out the original so that it was quite easy to edit the needed number changes per row
(but plum forgot to add the max() much to my horror)
0
 

Author Comment

by:soozh
Comment Utility
yes it was a rare mistake there!

But the rest of the columns don't need case statements - they are just data from the Trauma table.

i just wondered if it is better to use an aggregate function, or to include them in the Group by.

Using Group by will result in 50+ columns in the statement - but maybe thats not a problem?

What is most efficent?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
if there is no need to an aggregate function, don't use one.

so, here, just add the trauma table fields into the group by

A group by of 50 columns might seem like a lot, but my guess is that trauma.tra_id is a unique field in any case and I would expect the optimizer will recognize that - so because you a grouping by something that is already unique it probably isn't going to add a huge overhead.

If this assumption is wrong (i.e. it does slow down the query lots)
 - then there are 2 options
a. opt for max() on the recently added fields
b. you could introduce a join after the initial grouping is performed
- but I don't think that will be required.

if performance becomes an issue review the explain plan
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now