Need to make one row out of many

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

beparker@yahoo.com - 19 Mar 2008 01:14 GMT

DBMS: Microsoft SQL Server 2005

Issue: I have a table with clientIds and statuses in it. I need to
query from this table and return one row per clientId with columns for
each status holding the total number of records for that status for
that client.

Pertinent columns in the original table:
ClientID INT
StatusID INT

There are 6 possible statuses: 1, 2, 3, 4, 5, 6

Rules:
======
-- I need a 0 count (NULL would work if necessary) if there are no
records for that client for that status
-- Can assume the statuses are static for now. A dynamic solution
would be great, but not needed

Example of the data:
ClientID StatusID
1 1
1 1
1 2
1 4
1 4
1 6
2 3
2 3
2 3
2 4
2 4
2 4
3 2
3 3
3 4
3 5
3 6

What I am looking to be returned:
ClientID S1 S2 S3 S4 S5 S6
------------------------------------------------------
1 2 1 0 2 0 1
2 0 0 3 3 0 0
3 0 1 1 1 1 1

Any and all help is appreciated.

Thanks,
-BEP

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 19 Mar 2008 02:31 GMT

You need to know which statusID values to sum in each column, so it can't
really be "dynamic"...

SELECT
ClientID,
S1 = SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END),
S2 = SUM(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END),
S3 = SUM(CASE WHEN StatusID = 3 THEN 1 ELSE 0 END),
S4 = SUM(CASE WHEN StatusID = 4 THEN 1 ELSE 0 END),
S5 = SUM(CASE WHEN StatusID = 5 THEN 1 ELSE 0 END),
S6 = SUM(CASE WHEN StatusID = 6 THEN 1 ELSE 0 END)
FROM
table_name
GROUP BY ClientID;

You could also do this with the new PIVOT operator in SQL Server 2005.
However it too cannot really be "dynamic"...

SELECT
ClientID,
[1], [2], [3], [4], [5], [6]
FROM
(
SELECT ClientID, StatusID
FROM table_name
) t
PIVOT
(
COUNT(t.StatusID)
FOR t.StatusID IN ([1],[2],[3],[4],[5],[6])
) p
ORDER BY ClientID;

> DBMS: Microsoft SQL Server 2005
>
[quoted text clipped - 47 lines]
> Thanks,
> -BEP

Reply to this Message

beparker@yahoo.com - 19 Mar 2008 14:49 GMT

On Mar 18, 8:31 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:

> You need to know which statusID values to sum in each column, so it can't
> really be "dynamic"...
[quoted text clipped - 10 lines]
> table_name
> GROUP BY ClientID;

>snip<

Thanks, Aaron - it worked great!

Now, I have to add row numbering to it, which I know how to do, except
the ORDER BY in the OVER needs to be dynamic based on which of the 7
columns they want it sorted by. I'm adding numbering because they
want 1 "page" of data returned with "x" number of rows in it. So, if
they want Page 2 with a page size of 10, they want rows 11 - 20
returned. Below is how I think the numbering can be done, but I'm
hoping you guys can show me a better way:

-- Assume the above dataset is put in a temp table #tmpdata

SELECT ROW_NUMBER() OVER ( ORDER BY
CASE WHEN @SortByClientID = 1 AND @SortAscending = 1
THEN t.ClientID ELSE NULL
END,
CASE WHEN @SortByClientID = 1 AND @SortAscending = 0
THEN t.ClientID ELSE NULL
END DESC,
CASE WHEN @SortByS1 = 1 AND @SortAscending = 1
THEN t.S1 ELSE NULL
END,
CASE WHEN @SortByS1 = 1 AND @SortAscending = 0
THEN t.S1 ELSE NULL
END DESC,
-- S2 through S5 go here
CASE WHEN @SortByS6 = 1 AND @SortAscending = 1
THEN t.S6 ELSE NULL
END,
CASE WHEN @SortByS6 = 1 AND @SortAscending = 0
THEN t.S6 ELSE NULL
END DESC
) as RowNum,
ClientID,
S1,
S2,
S3,
S4,
S5,
S6
FROM @tmpdata t

Thanks again for the help.
-BEP

Reply to this Message