Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

t-sql pivot table issue

Posted on 2014-11-10
9
Medium Priority
?
160 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
Microsoft has released remote PowerShell capabilities to all commercial Office 365 customers. So you can be controlled via PowerShell and not from the Office 365 admin center Download Windows PowerShell Module for Lync Online http://www.micros…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

704 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