Solved

t-sql pivot table issue

Posted on 2014-11-10
9
159 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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

When you are trying to access the server, have you ever encountered "The terminal server has exceeded the maximum number of allowed connection" error?  or "The user is attempting to log on to a Terminal Server in Remote Administration mode, but the …
This is a fairly complicated script that will install the required prerequisites to install SCCM 2012 R2 on a server.  It was designed under the functional model in order to compartmentalize each step required, reducing the overall complexity.  The …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 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