Solved

t-sql pivot table issue

Posted on 2014-11-10
9
152 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
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
What help do you need?
0
 

Author Comment

by:jfreeman2010
Comment Utility
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
Comment Utility
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
 

Author Comment

by:jfreeman2010
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Look at lines 70 and 77 of the above code.
0
 

Author Closing Comment

by:jfreeman2010
Comment Utility
Thank you for helping!!!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Introduction: Sometimes when I receive a call from my users to solve their problems it is very difficult for me to found their computer IP address. Even finding their computer Host to provide remote support can be a problem.  So I resorted to Goo…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now