x

Transpose Columns to Rows in SQL 2012

I have my data (attached) in cloumns and I like to transpose them into rows. I tried Unipovt but since most of the fileds are dynamics and not fixed, I am having issues to set Unipivot dynamically. Here is current data in colums:

 create table #columnstorows
 
 (
 
 ThirdPartyPayer            varchar (5),
 
 PatientIDNumber            varchar (5),
 
 ClaimLineNumber            varchar (2),
 
 PaidAmount                    float                                
 
 )                                                             
 
 go                                                              
 
 insert into #columnstorows                                           
 
 (ThirdPartyPayer, PatientIDNumber,    ClaimLineNumber    ,PaidAmount)
 
 select  'U001','00001','01','76.58'     UNION ALL
 
 select  'U002','00002','02','19.53'     
 
 Go


and here are same data in one row:

 ThirdParty_1 ThirdParty_2 PatientIDNumber_1 PatientIDNumber_2 PaidAmount_1 Paid Amount_2 
 
 U001            U002          000001           000002              76.58          19.53

Any help will be appreciated,

Thank You

more ▼

asked Jul 29, 2015 at 12:14 AM in Default

avatar image

helal
20 8

Just to be clear, what is the desired output?

Jul 29, 2015 at 03:12 AM KenJ

Data depicted in one Row

Jul 29, 2015 at 02:07 PM helal
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
  DECLARE 
     @vcPatientID_List AS VARCHAR(500)
     ,@vcPatientID_Antet AS VARCHAR(500)
     ,@vcThirdParty_Antet AS VARCHAR(500)
     ,@vcPaidAmount_Antet AS VARCHAR(500)
     ,@nvcSQL AS NVARCHAR(MAX) =''
 
  SELECT @vcPatientID_List  =  ISNULL(@vcPatientID_List + ',' , '') +  
                     '[' + CAST(PatientIDNumber AS VARCHAR(50)) + ']' 
                     FROM #columnstorows
                     GRoUP BY PatientIDNumber
  SELECT @vcPatientID_Antet = ISNULL(@vcPatientID_Antet+',','') +
                     '[' + CAST(PatientIDNumber AS VARCHAR(50)) + '] AS ' +
                     'PatientIDNumber_' + CAST(A.RN AS VARCHAR(50))
                     FROM (SELECT PatientIDNumber 
                             , ROW_NUMBER() OVER(ORDER BY PatientIDNumber ASC) AS RN
                          FROM #columnstorows
                          GROUP BY PatientIDNumber)A
 SELECT @vcThirdParty_Antet = ISNULL(@vcThirdParty_Antet + ',' ,  '') + 
                     '[' + CAST(PatientIDNumber AS VARCHAR(50)) + '] AS ' +
                     'ThirdParty_' + CAST(A.RN AS VARCHAR(50))
                     FROM (SELECT PatientIDNumber 
                             , ROW_NUMBER() OVER(ORDER BY PatientIDNumber ASC) AS RN
                          FROM #columnstorows
                          GROUP BY PatientIDNumber)A
 SELECT @vcPaidAmount_Antet = ISNULL(@vcPaidAmount_Antet + ',' ,  '') + 
                     '[' + CAST(PatientIDNumber AS VARCHAR(50)) + '] AS ' +
                     'PaidAmount_' + CAST(A.RN AS VARCHAR(50))
                     FROM (SELECT PatientIDNumber 
                             , ROW_NUMBER() OVER(ORDER BY PatientIDNumber ASC) AS RN
                          FROM #columnstorows
                          GROUP BY PatientIDNumber)A
 
  PRINT @vcPatientID_Antet
  PRINT @vcPatientID_List
  PRINT @vcThirdParty_Antet
  PRINT @vcPaidAmount_Antet
 
  SET @nvcSQL = '
   SELECT
      B.*
      ,C.*
      ,A.*
  FROM
  (
      SELECT ' + @vcPaidAmount_Antet + '
      FROM (SELECT PatientIDNumber,PaidAmount FROM #columnstorows) AS CSR
      PIVOT (
         SUM(PaidAmount) FOR PatientIDNumber IN ('+ @vcPatientID_List + ')
      ) PV
  )A
  CROSS JOIN
  (
      SELECT ' + @vcThirdParty_Antet + '
      FROM (SELECT PatientIDNumber,ThirdPartyPayer FROM #columnstorows) AS CSR
      PIVOT(
         MAX(ThirdPartyPayer) FOR PatientIDNumber IN ('+ @vcPatientID_List + ')
       ) PV
  )B
  CROSS JOIN
  (
      SELECT '+@vcPatientID_Antet + '
      FROM (SELECT PatientIDNumber FROM #columnstorows) AS CSR
      PIVOT
      (
         MAX(PatientIDNumber) FOR PatientIDNumber IN ('+ @vcPatientID_List + ')
      ) PV
 )C'
 
 EXEC sp_ExecuteSQL @nvcSQL



and the output:

 ThirdParty_1 ThirdParty_2 PatientIDNumber_1 PatientIDNumber_2 PaidAmount_1 PaidAmount_2 
 U001    U002        00001         00002          76.58      19.53
more ▼

answered Jul 29, 2015 at 08:37 AM

avatar image

sabinweb
351 5 4

This is awesome. Two questions: 1- Are there two solutions one with Cross join and one without? or all part of one solution? 2 - How about the following scenario where only Thirdparty varies and Paidamount be summed up. Like: ThirdParty_1 ThirdParty_2 PatientIDNumber PaidAmount U001 U002 00001 96.11 Is this the case for using Pivot with dynamic variable for Thirdparty?

Thank You

Jul 29, 2015 at 02:13 PM helal

1-This is all part of one solution. I used CROSS JOIN to unify the results of the PIVOT

2 - Try it and see what you obtain (I don't have time right now)

Jul 29, 2015 at 02:18 PM sabinweb

I did and worked like a charm...Thank you. Anyway, I can put this #1 in a Temp Table?

Jul 29, 2015 at 04:28 PM helal

Just a quick questions: how do I add more static (not dynamic ) fields to the script? do I need to do the two parts the select and the cross join?

Jul 29, 2015 at 10:35 PM helal

To add more static columns, try to add them in SELECT PatientIDNumber,PaidAmount FROM #columnstorows) AS CSR and in the upper SELECT

Jul 30, 2015 at 04:37 AM sabinweb
(comments are locked)
10|1200 characters needed characters left

Thank you. I am running to a very strange situation. Sp works just fine with sample data, and real data, as long as I limit records to only a few. However, when apply to six month worth of data (10K+ records), I get a strange "Syntax Error" message. Here is the Script that works for limited records but not for few thousands. Error Message:

"Category Timestamp Duration Message Line Position Error 7/30/2015 2:16:08 PM 0:00:14.533 - SQL Server Database Error: Incorrect syntax near the keyword 'AS'. 624 0 "

Any Idea?

Sp Script:

// if object_id('tempdb..##PLPT') is not null begin drop table ##PLPT end //

DECLARE @ThirParty_List AS VARCHAR(128) ,@PatientID_Antet AS VARCHAR(128) ,@ThirdParty_Antet AS VARCHAR(128) ,@PaidAmount_Antet AS VARCHAR(128) ,@nvcSQL AS NVARCHAR(MAX) =''

SELECT @ThirParty_List = ISNULL(@ThirParty_List + ',' , '') +
'[' + CAST(ThirdPartyPayer AS VARCHAR(50)) + ']' FROM ##PLTAB Group BY ThirdPartyPayer
SELECT @PatientID_Antet = ISNULL(@PatientID_Antet+',','') + '[' + CAST(ThirdPartyPayer AS VARCHAR(50)) + '] AS ' + 'PatientIDNumber_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT ThirdPartyPayer
, ROW_NUMBER() OVER(ORDER BY ThirdPartyPayer ASC) AS RN FROM ##PLTAB GROUP BY ThirdPartyPayer)A SELECT @ThirdParty_Antet = ISNULL(@ThirdParty_Antet + ',' , '') + '[' + CAST(ThirdPartyPayer AS VARCHAR(50)) + '] AS ' + 'ThirdParty_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT ThirdPartyPayer , ROW_NUMBER() OVER(ORDER BY ThirdPartyPayer ASC) AS RN FROM ##PLTAB GROUP BY ThirdPartyPayer)A SELECT @PaidAmount_Antet = ISNULL(@PaidAmount_Antet + ',' , '') + '[' + CAST(ThirdPartyPayer AS VARCHAR(50)) + '] AS ' + 'PaidAmount_' + CAST(A.RN AS VARCHAR(50)) FROM (SELECT ThirdPartyPayer , ROW_NUMBER() OVER(ORDER BY ThirdPartyPayer ASC) AS RN FROM ##PLTAB GROUP BY ThirdPartyPayer)A

SET @nvcSQL = ' SELECT B. ,C. ,A.* INTO ##PLPT FROM ( SELECT ' + @PaidAmount_Antet + ' FROM (SELECT ThirdPartyPayer,PaidAmount FROM ##PLTAB) AS CSR PIVOT ( SUM(PaidAmount) FOR ThirdPartyPayer IN ('+ @ThirParty_List + ') ) PV )A CROSS JOIN ( SELECT ' + @ThirdParty_Antet + ' FROM (SELECT ThirdPartyPayer FROM ##PLTAB) AS CSR PIVOT( MAX(ThirdPartyPayer) FOR ThirdPartyPayer IN ('+ @ThirParty_List + ') ) PV )B CROSS JOIN ( SELECT '+@PatientID_Antet + ' FROM (SELECT PatientIDNumber,ThirdPartyPayer FROM ##PLTAB) AS CSR PIVOT ( MAX(PatientIDNumber) FOR ThirdPartyPayer IN ('+ @ThirParty_List + ') ) PV )C'

EXEC sp_ExecuteSQL @nvcSQL

select distinct * From ##PLPT

more ▼

answered Jul 30, 2015 at 08:18 PM

avatar image

helal
20 8

Add some clause to real data. Also keep in mind that you are using VARCHAR(128) - this could reach his upper limit, so try to increase it - like VARCHAR(4000)

Jul 31, 2015 at 04:45 AM sabinweb
(comments are locked)
10|1200 characters needed characters left

HI there, I have now a variation of the above scenario. Same structure except that PaidAmount is now split into six different columns (Paid1 to Paid6). Thus data loos like this:

3rdParty_1 3rdParty_2 PIDNo_1 PIDNo_2 Paid1 Paid2 Paid3 Paid4 Paid5 Paid6

U001 U002 00001 00002 50 20 10 5 0 NULL

How do I modify the codes to accommodates for the six paid amounts?

Thank You,

more ▼

answered Aug 14, 2015 at 11:19 PM

avatar image

helal
20 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x385
x112

asked: Jul 29, 2015 at 12:14 AM

Seen: 116 times

Last Updated: Aug 14, 2015 at 11:19 PM

Copyright 2017 Redgate Software. Privacy Policy