Query syntax

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

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

Select allOpen in new window

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!

>> 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
;

Select allOpen 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

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?

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

Select allOpen in new window

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.

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?

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!?

>>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)

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?

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