sql server - query one to many into one column

Question

Sooo in our database, there is a table of prosecution cases with a prosecution ID and case code. There is another table of court updates (( It's a one-to-many relationship in the sense that one prosecution-case can have multiple court updates))

I am trying to create a report to show cases and their multiple court updates and I don't know how to write a query that has the following columns - Litigation Case Code & Court Updates.

This is the sample prosecution table

╔════╦═════════════════════╗ ║ID ║ ProsecutionCaseCode ║ ╠════╬═════════════════════╣ ║ 1 ║CASE158SG ║ ╠════╬═════════════════════╣ ║ 2 ║CASE688ZY ║ ╠════╬═════════════════════╣ ║ 3 ║CASE999SS ║ ║ ║ ║ ╚════╩═════════════════════╝

This is the sample Court Update Table (CUid is court update Id)

╔════╦══════════════╦═════════════════════════════════════╗ ║CUid║ ProsecutionID║ UPDATE ║ ╠════╬════════════ ═╬═════════════════════════════════════╣ ║ 1 ║ 1 ║ 14th May - Judge wore pink ║ ╠════╬══════════════╬═════════════════════════════════════╣ ║ 2 ║ 1 ║ 13th May - Judge wore black ║ ╠════╬══════════════╬═════════════════════════════════════╣ ║ 3 ║ 1 ║ 12th May - Judge wore orange ║ ╠════╬══════════════╬═════════════════════════════════════╣ ║ 4 ║ 2 ║ 29th November - Judge was a no-show ║ ╠════╬══════════════╬═════════════════════════════════════╣ ║ 5 ║ 3 ║ 19th January - Judge is cute lol ║ ╠════╬══════════════╬═════════════════════════════════════╣ ║ 6 ║ 3 ║ 1st January - Judge was mean ║ ╚════╩══════════════╩═════════════════════════════════════╝

this is the report I am trying to create

╔════╦═════════════════════╦════════════════════════════════════════╗ ║SNO ║ ProsecutionCaseCode ║ COURT UPDATE ║ ╠════╬═════════════════════╬════════════════════════════════════════╣ ║ 1 ║CASE158SG ║ 14th May - Judge wore pink ║ ║ ║ ║ 13th May - Judge wore black ║ ║ ║ ║ 12th May - Judge wore orange ║ ╠════╬═════════════════════╬════════════════════════════════════════╣ ║ 2 ║CASE688ZY ║ 29th November - Judge was a no-show ║ ╠════╬═════════════════════╬════════════════════════════════════════╣ ║ 3 ║CASE999SS ║ 19th January - Judge is cute lol ║ ║ ║ ║ 1st January - Judge was mean ║ ╚════╩═════════════════════╩════════════════════════════════════════╝

This is what SSRS is used for, or any other reporting solution. — S3S, Nov 20 '17 at 16:45
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Code Different, Nov 20 '17 at 16:49
Starting with SQL Server 2017; you can use the [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) function. — David Rushton, Nov 20 '17 at 17:17

score 0 · Answer 1 · answered Nov 20 '17 at 16:50

0

you can use code below

select u.ProsecutionID as sno,p.ProsecutionCaseCode  ,u.UPDATE as [COURT UPDATE] from prosecution as p inner join Update as u on p.id=u.ProsecutionID
order by u.ProsecutionID,u.CUid

answered Nov 20 '17 at 16:50

hassan naeimi

119
4

score 0 · Answer 2 · answered Nov 20 '17 at 17:36

It's not pretty but you can use a sub-query within your select clause.

The hard part is concatenating multiple rows into one. I've used FOR XML for this.

Example:

SELECT
    p.ID,
    p.ProsecutionCaseCode,
    REPLACE(
        REPLACE(
            (
                -- Here we hijack the FOR XML clause to concatenate multiple values into one string.
                -- XML will append opend and closed UPDATE tags, as this is the column name.
                -- Replace functions strip these out.
                SELECT
                    [UPDATE]
                FROM
                    @CourtUpdate AS cu
                WHERE
                    cu.ProsecutionID = p.ID
                FOR XML PATH('')
            )
        , '<UPDATE>', '')
    , '</UPDATE>', CHAR(13)) AS [COURT UPDATE]
FROM
    @prosecution AS p       
ORDER BY
    p.ID
;

This approach uses a correlated subquery. These can hurt performance, as they force SQL Server to process row by row. Ugh.

By default SSMS only shows the first line, in the results grid. Press Ctrl + T to switch to results to text mode. From here you can view the full results, without copying/pasting.

SQL Server 2017 includes a new function called string_agg At the time of writing, this is pretty new. Here's a (not tested) example:

-- SQL Server 2017 or higher.
SELECT
    p.ID,
    p.ProsecutionCaseCode,
    STRING_AGG(cu.ProsecutionID,  CHAR(13)) AS [COURT UPDATE]
FROM
    @prosecution AS p   
        INNER JOIN @CourtUpdate AS cu       ON cu.ProsecutionID = p.ID
GROUP BY
    p.ID,
    p.ProsecutionCaseCode
ORDER BY
    p.ID
;

Much better.

score 0 · Answer 3 · edited Nov 20 '17 at 21:06

SQL Server 2012 or higher

SELECT PT.ID, 
        PT.ProsecutionCaseCode,
        CUT.UPDATE,
FROM ProsecutionTable AS PT
LEFT OUTER JOIN CourtUpdateTable AS CUT
    ON PT.ID = CUT.ProsecutionID
ORDER BY PD.ID,
         PT.ProsecutionCaseCode

I would strongly recommend avoiding doing an INNER JOIN in this case. With a LEFT OUTER JOIN, you will guarantee showing all ProsecutionCaseCodes, regardless of them containing an entry in CourtUpdateTable.

sql server - query one to many into one column

3 Answers3