SQL PIVOT result set

Question

Looking for help with pivoting a result set as I'm very new to it.

Here I have test data inserted into a table.

        CREATE TABLE #temp (procCode int, member_id varchar(10))

        INSERT INTO #temp(procCode,member_id)
        SELECT 90658,'jjjj'
        UNION all
        SELECT 90658,'k'
        UNION all
        SELECT 90658,'jjjkk'
        UNION all
        SELECT 90658,'jjjj'
        UNION all
        SELECT 90658,'k'
        UNION all
        SELECT 90658,'jjjkk'
        UNION all
        SELECT 90658,'jjjj'
        UNION all
        SELECT 90658,'k'
        UNION all
        SELECT 90649,'jjjj'
        UNION all
        SELECT 90649,'k'
        UNION all
        SELECT 906,'jjjj'
        UNION all
        SELECT 906,'jjjj'


        select 
         member_id,procCode, COUNT(*) as countProcCode
        FROM #temp
        GROUP BY member_id,procCode

This right now outputs data like this:

member_id      procCode     CountProcCode
jjjj             906               2       
jjjj             90649             1
jjjkk             90658            2
  k              90649             1
jjjj             90658             3   
 k                90658            3

How I need it to display is like this:

member_id    Count906    count90649     count90658
jjjj         2              1              3
k             0             1              3
jjjkk         0             0              2

Any help is greatly appreciated. There are more than just these procCodes and member_id so I couldn't really say where member_id in (506,50658,50649) as there are additional ones that could appear.

score 0 · Accepted Answer · answered Sep 24 '14 at 05:28

0

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.procCode) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT member_id, ' + @cols + ' from 
            (
                select member_id, member_id as b
                    , procCode

                from #temp
           ) x
            pivot 
            (
                 count(b)
                for procCode in (' + @cols + ')
            ) p '


execute(@query)

drop table #temp

Results

member_id   906 90649   90658
jjjj        2   1       3
jjjkk       0   0       2
k           0   1       3

Recomended reading

answered Sep 24 '14 at 05:28

Horaciux

6,322
2
22
41

3

How very appropriate! Not only the question turned out to be a duplicate of the linked one, but also your answer looks almost like an exact copy of the accepted answer to the linked question. You even used same variable names and formatting, just amazing! – Andriy M Sep 24 '14 at 12:03
@AndriyM Sorry if this offended someone. It was hard to me to find a solution even based on previous solution.I'm learning SQL by solving issues in SO. As it turns hard to me in those terms, maybe op was struggle with the same and turns out to be useful for him share what I found. And also check if it is a valid solution via your up/down vote and explanations/critics. – Horaciux Sep 24 '14 at 12:13
I, too, am sorry about my probably a bit over-the-top sarcasm. I appreciate that you didn't just blindly copy the solution you found but instead put some effort into adapting it for the OP's needs. The primary issue that caused me to comment in the first place, however, is that your solution is so clearly appears to be based on the other answer and yet you didn't credit the original author with a single word or link. – Andriy M Sep 24 '14 at 12:33
@AndriyM I added a link to original autor from 2007, there is where previous answer was base (exactly the same code and no link!!!) Why down votes? Is any thing wrong with this solution I need to know or is it some kind of punishment? If anyone have a doubt about my commitment verify time zone, it was 2am when I finally get it (matching results and expected results). – Horaciux Sep 24 '14 at 12:54
Can't help you with the downvotes, sorry, none of them is mine. But are you sure you used *only* the site you've linked in your post? I can't find a query on that page that would resemble yours in all the details so well as the answer in the linked question does. Just saying. I've answered questions on SO in the deep of the night too, you know, and I'm aware how it can be, so my guess is maybe you did use that article in your explorations but maybe you forgot that you *also* used some of the many answers by the linked answer's author (bluefeet). (Yes, maybe not the linked one but some other.) – Andriy M Sep 24 '14 at 13:43
As I said, I couldn't find a solution even based on previous solution (from blufeet, which I up voted if that count for something) Then I came to the link I recommend reading -that turns out to be the source of the source. When solved, I posted here to help OP and find out if it was completely right or can be enhanced in some way. I still don't know because silent down votes don't help – Horaciux Sep 24 '14 at 14:27

SQL PIVOT result set

1 Answers1