Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

t-sql pivot table issue

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
jfreeman2010
Asked:
jfreeman2010
  • 5
  • 4
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What help do you need?
0
 
jfreeman2010Author Commented:
I need help on UNION ALL columns from the result of PIVOT Table and #TMP_TB_1 table.  thanks,
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
jfreeman2010Author Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
jfreeman2010Author Commented:
yes, I much missing some code.  Thank you!!


The the result insert to a temp table?  and how?  Thank you!!
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Look at lines 70 and 77 of the above code.
0
 
jfreeman2010Author Commented:
Thank you for helping!!!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now