Solved

t-sql pivot table issue

Posted on 2014-11-10
9
158 Views
Last Modified: 2014-11-11
HI,

I created the following t-sql example code, the result from a PIVOT table
will needs to UNION ALL columns with #TMP_TB_1, the final result will have the following cols:

APPMT_ID, LAST_FIRST_NAME, + COLS OF SRV_NAME.... + DOB, SEX

I need help on how to UNION ALL columns from a PIVOT Table and other table.  Thanks you.

 

 
CREATE TABLE #TMP_TB
(
      APPMT_ID                              INT,
      SRV_ID                                    INT,
      SRV_NAME                              VARCHAR(50)

)

CREATE TABLE #TMP_TB_1
(
      APPMT_ID                              INT,
      LAST_FIRST_NAME                        VARCHAR(50),
      DOB                                          VARCHAR(10),
      SEX                                          CHAR(1)

)

 insert into #TMP_TB_1 values(16862, 'BN, John', '1980-12-01', 'M');
 insert into #TMP_TB_1 values(16261, 'BR, Joe', '1969-01-20', 'M');
 insert into #TMP_TB_1 values(16866, 'CH, Mary', '1974-03-05', 'F');
 insert into #TMP_TB_1 values(17005, 'ED, JoAnn', '1985-10-11', 'F');
 insert into #TMP_TB_1 values(16869, 'FL, Kent', '1999-08-14', 'M');
 insert into #TMP_TB_1 values(14806, 'GO, Anna', '1994-05-26', 'F');
 insert into #TMP_TB_1 values(16867, 'JZ, Barbara', '1984-09-13', 'F');



 insert into #TMP_TB values(16862, 2, 'HP');
 insert into #TMP_TB values(16862, 49, 'PSA');
 insert into #TMP_TB values(16862, 136, 'A1c');
 insert into #TMP_TB values(16261, 2, 'HP');
 insert into #TMP_TB values(16866, 2, 'HP');
 insert into #TMP_TB values(16866, 136, 'A1c');
 insert into #TMP_TB values(16866, 281, 'Nicotine');
 insert into #TMP_TB values(17005, 2, 'HP');
 insert into #TMP_TB values(17005, 281, 'Nicotine');
 insert into #TMP_TB values(16869, 2, 'HP');
 insert into #TMP_TB values(16869, 49, 'PSA');
 insert into #TMP_TB values(16869, 136, 'A1c');
 insert into #TMP_TB values(14806, 2, 'HP');
 insert into #TMP_TB values(14806, 133, 'TSH');
 insert into #TMP_TB values(14806, 281, 'Nicotine');
 insert into #TMP_TB values(16867, 2, 'HP');
 insert into #TMP_TB values(16867, 133, 'TSH');
 insert into #TMP_TB values(16867, 136, 'A1c');
 insert into #TMP_TB values(16867, 281, 'Nicotine');


 DECLARE @columns VARCHAR(max),
      @query VARCHAR(max)
                  ;

      SELECT
            @columns = COALESCE(@columns + ',[' + cast(SRV_NAME as varchar) + ']',
            '[' + cast(SRV_NAME as varchar)+ ']')
            FROM #TMP_TB
            GROUP BY SRV_NAME
            ;


      SET @query = '
      SELECT      *
      FROM #TMP_TB
      PIVOT
      (
      MAX(SRV_ID)
      FOR [SRV_NAME]
      IN (' + @columns + ')
      ) X
      '

      EXECUTE(@query)


 DROP TABLE #TMP_TB;
0
Comment
Question by:jfreeman2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434693
What help do you need?
0
 

Author Comment

by:jfreeman2010
ID: 40434826
I need help on UNION ALL columns from the result of PIVOT Table and #TMP_TB_1 table.  thanks,
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434852
You are using the term UNION ALL wrongly, and that was confusing me. I'm easily confused :-)

UNION ALL is combination of rows. What you want is the combination of columns, and that is a JOIN.

Please find below the updated part of your SET @query code:

     SET @query = '
      WITH myTable as (SELECT      *
      FROM #TMP_TB
      PIVOT
      (
      MAX(SRV_ID)
      FOR [SRV_NAME]
      IN (' + @columns + ')
      ) X)
	  SELECT a.*, ' + @columns + ' FROM myTable
	  LEFT JOIN #TMP_TB_1 a
	  ON myTable.APPMT_ID = a.APPMT_ID
      '

Open in new window


You may also want at the end of the code

  DROP TABLE #TMP_TB_1;

Open in new window


as well.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:jfreeman2010
ID: 40434863
Hi Phillip,

Thank you very much for help.  I try it and the result only shows the #TMP_TB_1 columns, not sure why.  Any also can the combine result insert to a temp table?  those result need to be further processing.

Thanks!!!
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434867
I get this answer:

APPMT_ID    LAST_FIRST_NAME                                    DOB        SEX  A1c         HP          Nicotine    PSA         TSH
----------- -------------------------------------------------- ---------- ---- ----------- ----------- ----------- ----------- -----------
14806       GO, Anna                                           1994-05-26 F    NULL        2           281         NULL        133
16261       BR, Joe                                            1969-01-20 M    NULL        2           NULL        NULL        NULL
16862       BN, John                                           1980-12-01 M    136         2           NULL        49          NULL
16866       CH, Mary                                           1974-03-05 F    136         2           281         NULL        NULL
16867       JZ, Barbara                                        1984-09-13 F    136         2           281         NULL        133
16869       FL, Kent                                           1999-08-14 M    136         2           NULL        49          NULL
17005       ED, JoAnn                                          1985-10-11 F    NULL        2           281         NULL        NULL

Open in new window


Here's the full code I've got:

CREATE TABLE #TMP_TB
(
      APPMT_ID                              INT,
      SRV_ID                                    INT,
      SRV_NAME                              VARCHAR(50)

)

CREATE TABLE #TMP_TB_1
(
      APPMT_ID                              INT,
      LAST_FIRST_NAME                        VARCHAR(50),
      DOB                                          VARCHAR(10),
      SEX                                          CHAR(1)

)

 insert into #TMP_TB_1 values(16862, 'BN, John', '1980-12-01', 'M');
 insert into #TMP_TB_1 values(16261, 'BR, Joe', '1969-01-20', 'M');
 insert into #TMP_TB_1 values(16866, 'CH, Mary', '1974-03-05', 'F');
 insert into #TMP_TB_1 values(17005, 'ED, JoAnn', '1985-10-11', 'F');
 insert into #TMP_TB_1 values(16869, 'FL, Kent', '1999-08-14', 'M');
 insert into #TMP_TB_1 values(14806, 'GO, Anna', '1994-05-26', 'F');
 insert into #TMP_TB_1 values(16867, 'JZ, Barbara', '1984-09-13', 'F');



 insert into #TMP_TB values(16862, 2, 'HP'); 
 insert into #TMP_TB values(16862, 49, 'PSA'); 
 insert into #TMP_TB values(16862, 136, 'A1c'); 
 insert into #TMP_TB values(16261, 2, 'HP'); 
 insert into #TMP_TB values(16866, 2, 'HP'); 
 insert into #TMP_TB values(16866, 136, 'A1c'); 
 insert into #TMP_TB values(16866, 281, 'Nicotine'); 
 insert into #TMP_TB values(17005, 2, 'HP');
 insert into #TMP_TB values(17005, 281, 'Nicotine');
 insert into #TMP_TB values(16869, 2, 'HP'); 
 insert into #TMP_TB values(16869, 49, 'PSA'); 
 insert into #TMP_TB values(16869, 136, 'A1c'); 
 insert into #TMP_TB values(14806, 2, 'HP'); 
 insert into #TMP_TB values(14806, 133, 'TSH'); 
 insert into #TMP_TB values(14806, 281, 'Nicotine'); 
 insert into #TMP_TB values(16867, 2, 'HP'); 
 insert into #TMP_TB values(16867, 133, 'TSH'); 
 insert into #TMP_TB values(16867, 136, 'A1c');
 insert into #TMP_TB values(16867, 281, 'Nicotine'); 


 DECLARE @columns VARCHAR(max),
      @query VARCHAR(max)
                  ;

      SELECT
            @columns = COALESCE(@columns + ',[' + cast(SRV_NAME as varchar) + ']',
            '[' + cast(SRV_NAME as varchar)+ ']')
            FROM #TMP_TB
            GROUP BY SRV_NAME
            ;


      SET @query = '
      WITH myTable as (SELECT      *
      FROM #TMP_TB
      PIVOT
      (
      MAX(SRV_ID)
      FOR [SRV_NAME]
      IN (' + @columns + ')
      ) X)
	  SELECT a.*, ' + @columns + ' FROM myTable
	  LEFT JOIN #TMP_TB_1 a
	  ON myTable.APPMT_ID = a.APPMT_ID
      '

      EXECUTE(@query)


 DROP TABLE #TMP_TB;
  DROP TABLE #TMP_TB_1;

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434881
How about this:

CREATE TABLE #TMP_TB
(
      APPMT_ID                              INT,
      SRV_ID                                    INT,
      SRV_NAME                              VARCHAR(50)

)

CREATE TABLE #TMP_TB_1
(
      APPMT_ID                              INT,
      LAST_FIRST_NAME                        VARCHAR(50),
      DOB                                          VARCHAR(10),
      SEX                                          CHAR(1)

)

 insert into #TMP_TB_1 values(16862, 'BN, John', '1980-12-01', 'M');
 insert into #TMP_TB_1 values(16261, 'BR, Joe', '1969-01-20', 'M');
 insert into #TMP_TB_1 values(16866, 'CH, Mary', '1974-03-05', 'F');
 insert into #TMP_TB_1 values(17005, 'ED, JoAnn', '1985-10-11', 'F');
 insert into #TMP_TB_1 values(16869, 'FL, Kent', '1999-08-14', 'M');
 insert into #TMP_TB_1 values(14806, 'GO, Anna', '1994-05-26', 'F');
 insert into #TMP_TB_1 values(16867, 'JZ, Barbara', '1984-09-13', 'F');



 insert into #TMP_TB values(16862, 2, 'HP'); 
 insert into #TMP_TB values(16862, 49, 'PSA'); 
 insert into #TMP_TB values(16862, 136, 'A1c'); 
 insert into #TMP_TB values(16261, 2, 'HP'); 
 insert into #TMP_TB values(16866, 2, 'HP'); 
 insert into #TMP_TB values(16866, 136, 'A1c'); 
 insert into #TMP_TB values(16866, 281, 'Nicotine'); 
 insert into #TMP_TB values(17005, 2, 'HP');
 insert into #TMP_TB values(17005, 281, 'Nicotine');
 insert into #TMP_TB values(16869, 2, 'HP'); 
 insert into #TMP_TB values(16869, 49, 'PSA'); 
 insert into #TMP_TB values(16869, 136, 'A1c'); 
 insert into #TMP_TB values(14806, 2, 'HP'); 
 insert into #TMP_TB values(14806, 133, 'TSH'); 
 insert into #TMP_TB values(14806, 281, 'Nicotine'); 
 insert into #TMP_TB values(16867, 2, 'HP'); 
 insert into #TMP_TB values(16867, 133, 'TSH'); 
 insert into #TMP_TB values(16867, 136, 'A1c');
 insert into #TMP_TB values(16867, 281, 'Nicotine'); 


 DECLARE @columns VARCHAR(max),
      @query VARCHAR(max)
                  ;

      SELECT
            @columns = COALESCE(@columns + ',[' + cast(SRV_NAME as varchar) + ']',
            '[' + cast(SRV_NAME as varchar)+ ']')
            FROM #TMP_TB
            GROUP BY SRV_NAME
            ;


      SET @query = '
      WITH myTable as (SELECT      *
      FROM #TMP_TB
      PIVOT
      (
      MAX(SRV_ID)
      FOR [SRV_NAME]
      IN (' + @columns + ')
      ) X)
	  SELECT a.*, ' + @columns + ' 	  into TMP_TC FROM myTable
	  LEFT JOIN #TMP_TB_1 a
	  ON myTable.APPMT_ID = a.APPMT_ID
      '

      EXECUTE(@query)

	  select * from TMP_TC

 DROP TABLE #TMP_TB;
  DROP TABLE #TMP_TB_1
  DROP TABLE TMP_TC;

Open in new window


This gives the following results:

APPMT_ID    LAST_FIRST_NAME                                    DOB        SEX  A1c         HP          Nicotine    PSA         TSH
----------- -------------------------------------------------- ---------- ---- ----------- ----------- ----------- ----------- -----------
14806       GO, Anna                                           1994-05-26 F    NULL        2           281         NULL        133
16261       BR, Joe                                            1969-01-20 M    NULL        2           NULL        NULL        NULL
16862       BN, John                                           1980-12-01 M    136         2           NULL        49          NULL
16866       CH, Mary                                           1974-03-05 F    136         2           281         NULL        NULL
16867       JZ, Barbara                                        1984-09-13 F    136         2           281         NULL        133
16869       FL, Kent                                           1999-08-14 M    136         2           NULL        49          NULL
17005       ED, JoAnn                                          1985-10-11 F    NULL        2           281         NULL        NULL

Open in new window

0
 

Author Comment

by:jfreeman2010
ID: 40434885
yes, I much missing some code.  Thank you!!


The the result insert to a temp table?  and how?  Thank you!!
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40434905
Look at lines 70 and 77 of the above code.
0
 

Author Closing Comment

by:jfreeman2010
ID: 40434924
Thank you for helping!!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Back in July, I blogged about how Microsoft's new server pricing model, combined with the end of the Small Business Server package, would result in significant cost increases for many small businesses (see SBS End of Life: Microsoft Punishes Small B…
Experts-Exchange users below are the steps you can follow to upgrade your Lync server to latest CU's or cumulative updates. Note: Perform it during non-production hours.   Step 1: Backup your lync and SQL server database. Follow below article: h…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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