Solved

Query syntax

Posted on 2013-11-03
13
179 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
ID: 39620026
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
ID: 39620036
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
ID: 39620069
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39620926
>> 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
ID: 39623695
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
ID: 39624040
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39624123
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
ID: 39624576
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
ID: 39625852
>>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
ID: 39626754
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
ID: 39627079
>>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
ID: 39627128
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
ID: 39627144
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

778 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