Solved

sql query for Pivot

Posted on 2015-02-24
33
59 Views
Last Modified: 2015-03-05
fieldname      oldvalue      newvalue
[PLAN]      OLD1      NEW1
[RS]      OLD2      NEW2
[SMOKER]      OLD2      NEW2
[PAR]      OLD3      NEW4
[SEX]      OLD4      NEW5
[AGE]      OLD5      NEW6
[REIN TYPE]      OLD6      NEW7
[RTT ]      OLD7      NEW8
[REIN CO]      OLD9      NEW9
[PREM DUR OFF]      OLD10      NEW10
[CESS]      OLD11      NEW11
[EFF-DATE]      OLD12      NEW12
[PD-TO-DT]      OLD13      NEW13
[ME-DATE]      OLD14      NEW14
[ORG-CESS-AMT]      OLD15      NEW15
[BASE-TTY]      OLD16      NEW16
[WP-FACE-AMT]      OLD17      NEW17
[WP-TTY]      OLD18      NEW18
[AD-FACE-AMT]      OLD19      NEW19
[AD-TTY ]      OLD20      NEW20
[FLAT-EX]      OLD21      NEW21
[FLAT-EX-TTY]      OLD22      NEW22
[RETEN]      OLD23      NEW23
[BILL ADJ FLG]      OLD      NEW25
[BILL-AMOUNT ]      OLD7      NEW26
[BILL-ALLOW  ]      OLD7      NEW27
[REST PREM   ]      OLD7      NEW28
[TOT-PREM    ]      OLD7      NEW29
[TOT-PREM    ]      OLD7      NEW30
[CESS-AMT    ]      OLD7      NEW31
[DECR        ]      OLD7      NEW32
[LIFE-PREM  ]      OLD7      NEW33
[POL-FEE   ]      OLD7      NEW34
[AD-PREM     ]      OLD7      NEW35
[WP-PREM     ]      OLD7      NEW36
[EXTRA-PREM  ]      OLD7      NEW37
[ALLOWANCE   ]      OLD7      NEW38
[NOTIFY-DT  ]      OLD7      NEW39
[RSN     ]      OLD7      NEW40
[STAT   ]      OLD7      NEW41
[PREV STAT   ]      OLD42      NEW7
[YTD GROS PRM]      OLD43      NEW7
[MULT PREM   ]      OLD44      NEW7
[ALLOW EXCESS]      OLD45      NEW7

how can i convert above  columns in to rows
0
Comment
Question by:BeyondBGCM
  • 16
  • 11
  • 4
  • +1
33 Comments
 

Author Comment

by:BeyondBGCM
Comment Utility
such that , [RS]      OLD2      NEW2 values should be  

[RS]
OLD2      
NEW2
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
with CTETable as (
Select FileName as myColumn, ROW_NUMBER() OVER(ORDER BY (Select 1)) as myRowNumber, 1 as myOrder
From myTable
UNION ALL
Select oldvalue, ROW_NUMBER() OVER(ORDER BY (Select 1)) as myRowNumber, 2 as myOrder
From myTable
UNION ALL
Select newvalue, ROW_NUMBER() OVER(ORDER BY (Select 1)) as myRowNumber, 3 as myOrder
From myTable)
Select FileName
from CTETable
Order by myRowNumber, myOrder

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
You can try this:
create table #tmp(fieldname varchar(25),oldvalue varchar(50), newvalue varchar(50))
insert into #tmp
select 'PLAN', 'old1','new1' union all
select 'RS', 'old2','new2' union all
select 'SMOKER', 'old3','new3' union all
select 'PAR', 'old4','new4' union all
select 'SEX', 'old5','new5'
-- ...

select * from #tmp

declare
	@sql_fieldname varchar(max)='',
	@sql_olds varchar(max)='',
	@sql_news varchar(max)='',
	@sql_final varchar(max)

select 
	@sql_fieldname=@sql_fieldname+''''+fieldname+''',',
	@sql_olds=@sql_olds++''''+oldvalue+''',',
	@sql_news=@sql_news++''''+newvalue+''','
from 
	#tmp
select 
	@sql_final=	'select '+replace(@sql_fieldname+'@#$',',@#$','')+' UNION ALL '+
				'select '+replace(@sql_olds+'@#$',',@#$','')+' UNION ALL '+
				'select '+replace(@sql_news+'@#$',',@#$','')

exec(@sql_final)

Open in new window

I assumed that your old and new values are varchar type as well. If not you need to add CAST around them in the dynamic part.
0
 

Author Comment

by:BeyondBGCM
Comment Utility
i want to question Phillip
how can we use this query with Pivot , if you look at the example shared in below url , from MSDN , we can convert columns in to rows with pivot and unpivot ,

can we talk about it .
0
 

Author Comment

by:BeyondBGCM
Comment Utility
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
And what do you want to talk about?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Are you expecting one column for every row of that sample data?

here's an example of just 10 rows converted to columns
| ROWNO |      1 |    2 |        3 |     4 |     5 |     6 |           7 |      8 |         9 |             10 |
|-------|--------|------|----------|-------|-------|-------|-------------|--------|-----------|----------------|
|     1 | [PLAN] | [RS] | [SMOKER] | [PAR] | [SEX] | [AGE] | [REIN TYPE] | [RTT ] | [REIN CO] | [PREM DUR OFF] |
|     2 |   OLD1 | OLD2 |     OLD2 |  OLD3 |  OLD4 |  OLD5 |        OLD6 |   OLD7 |      OLD9 |          OLD10 |
|     3 |   NEW1 | NEW2 |     NEW2 |  NEW4 |  NEW5 |  NEW6 |        NEW7 |   NEW8 |      NEW9 |          NEW10 |

Open in new window


That was produced by this query:
SELECT rowno, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
FROM (
      select ca.recno, ca.rowno, ca.value
      from (
             select *, row_number() over(order by (select 1)) as recno from table1
           ) x
      cross apply (
                  values  
                          (recno,1,[fieldname]),
                          (recno,2,[oldvalue] ),
                          (recno,3,[newvalue] )
                  ) ca (recno, rowno, value)  
     )  AS SourceTable
PIVOT
    (
      MAX(value)
      FOR recno IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
    ) AS PivotTable
;

Open in new window


The point of this example is that I can do this rather easily IF the column headings are known in advance and do not change. In this example you see those as [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]

i.e. if you want the data to be the headings, and the range of data changes, then you can't use static sql and are forced to dynamic sql. This is also made a little awkward by having data the includes the square brackets

This result:
| ROWNO | PLAN |   RS | SMOKER |  PAR |  SEX |  AGE | REIN TYPE | RTT  | REIN CO | PREM DUR OFF |
|-------|------|------|--------|------|------|------|-----------|------|---------|--------------|
|     1 | OLD1 | OLD2 |   OLD2 | OLD3 | OLD4 | OLD5 |      OLD6 | OLD7 |    OLD9 |        OLD10 |
|     2 | NEW1 | NEW2 |   NEW2 | NEW4 | NEW5 | NEW6 |      NEW7 | NEW8 |    NEW9 |        NEW10 |

Open in new window


Is produced by this query:
SELECT rowno, [PLAN], [RS], [SMOKER], [PAR], [SEX], [AGE], [REIN TYPE], [RTT ], [REIN CO], [PREM DUR OFF]
FROM (
      select ca.fieldname, ca.rowno, ca.value
      from (
             select
                    replace(replace([fieldname],'[',''),']','') as fieldname
                  , [oldvalue]
                  , [newvalue]
             from table1
           ) x
      cross apply (
                  values  
                          (fieldname,1,[oldvalue] ),
                          (fieldname,2,[newvalue] )
                  ) ca (fieldname, rowno, value)  
     )  AS SourceTable
PIVOT
    (
      MAX(value)
      FOR fieldname IN ([PLAN], [RS], [SMOKER], [PAR], [SEX], [AGE], [REIN TYPE], [RTT ], [REIN CO], [PREM DUR OFF])
    ) AS PivotTable
;

Open in new window


working details:
**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE Table1
    	([fieldname] varchar(14), [oldvalue] varchar(5), [newvalue] varchar(5))
    ;
    	
    INSERT INTO Table1
    	([fieldname], [oldvalue], [newvalue])
    VALUES
    	('[PLAN]', 'OLD1', 'NEW1'),
    	('[RS]', 'OLD2', 'NEW2'),
    	('[SMOKER]', 'OLD2', 'NEW2'),
    	('[PAR]', 'OLD3', 'NEW4'),
    	('[SEX]', 'OLD4', 'NEW5'),
    	('[AGE]', 'OLD5', 'NEW6'),
    	('[REIN TYPE]', 'OLD6', 'NEW7'),
    	('[RTT ]', 'OLD7', 'NEW8'),
    	('[REIN CO]', 'OLD9', 'NEW9'),
    	('[PREM DUR OFF]', 'OLD10', 'NEW10')
    ;

**Query 1**:

    select quotename(fieldname) from table1
    

**[Results][2]**:
    
    |          COLUMN_0 |
    |-------------------|
    |         [[PLAN]]] |
    |           [[RS]]] |
    |       [[SMOKER]]] |
    |          [[PAR]]] |
    |          [[SEX]]] |
    |          [[AGE]]] |
    |    [[REIN TYPE]]] |
    |         [[RTT ]]] |
    |      [[REIN CO]]] |
    | [[PREM DUR OFF]]] |


**Query 2**:

    SELECT rowno, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
    FROM (
          select ca.recno, ca.rowno, ca.value
          from (
                 select *, row_number() over(order by (select 1)) as recno from table1
               ) x
          cross apply (
                      values  
                              (recno,1,[fieldname]),
                              (recno,2,[oldvalue] ),
                              (recno,3,[newvalue] )
                      ) ca (recno, rowno, value)  
         )  AS SourceTable
    PIVOT
        (
          MAX(value)
          FOR recno IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
        ) AS PivotTable
    

**[Results][3]**:
    
    | ROWNO |      1 |    2 |        3 |     4 |     5 |     6 |           7 |      8 |         9 |             10 |
    |-------|--------|------|----------|-------|-------|-------|-------------|--------|-----------|----------------|
    |     1 | [PLAN] | [RS] | [SMOKER] | [PAR] | [SEX] | [AGE] | [REIN TYPE] | [RTT ] | [REIN CO] | [PREM DUR OFF] |
    |     2 |   OLD1 | OLD2 |     OLD2 |  OLD3 |  OLD4 |  OLD5 |        OLD6 |   OLD7 |      OLD9 |          OLD10 |
    |     3 |   NEW1 | NEW2 |     NEW2 |  NEW4 |  NEW5 |  NEW6 |        NEW7 |   NEW8 |      NEW9 |          NEW10 |


**Query 3**:

    SELECT rowno, [PLAN], [RS], [SMOKER], [PAR], [SEX], [AGE], [REIN TYPE], [RTT ], [REIN CO], [PREM DUR OFF]
    FROM (
          select ca.fieldname, ca.rowno, ca.value
          from (
                 select
                        replace(replace([fieldname],'[',''),']','') as fieldname
                      , [oldvalue]
                      , [newvalue]
                 from table1
               ) x
          cross apply (
                      values  
                              (fieldname,1,[oldvalue] ),
                              (fieldname,2,[newvalue] )
                      ) ca (fieldname, rowno, value)  
         )  AS SourceTable
    PIVOT
        (
          MAX(value)
          FOR fieldname IN ([PLAN], [RS], [SMOKER], [PAR], [SEX], [AGE], [REIN TYPE], [RTT ], [REIN CO], [PREM DUR OFF])
        ) AS PivotTable
    

**[Results][4]**:
    
    | ROWNO | PLAN |   RS | SMOKER |  PAR |  SEX |  AGE | REIN TYPE | RTT  | REIN CO | PREM DUR OFF |
    |-------|------|------|--------|------|------|------|-----------|------|---------|--------------|
    |     1 | OLD1 | OLD2 |   OLD2 | OLD3 | OLD4 | OLD5 |      OLD6 | OLD7 |    OLD9 |        OLD10 |
    |     2 | NEW1 | NEW2 |   NEW2 | NEW4 | NEW5 | NEW6 |      NEW7 | NEW8 |    NEW9 |        NEW10 |


**Query 4**:

        select ca.recno, ca.rowno, ca.value
        from (
               select *, row_number() over(order by (select 1)) as recno from table1
             ) x
        cross apply (
                    values  
                            (recno,1,[fieldname]),
                            (recno,2,[oldvalue] ),
                            (recno,3,[newvalue] )
                    ) ca (recno, rowno, value)  
    

**[Results][5]**:
    
    | RECNO | ROWNO |          VALUE |
    |-------|-------|----------------|
    |     1 |     1 |         [PLAN] |
    |     1 |     2 |           OLD1 |
    |     1 |     3 |           NEW1 |
    |     2 |     1 |           [RS] |
    |     2 |     2 |           OLD2 |
    |     2 |     3 |           NEW2 |
    |     3 |     1 |       [SMOKER] |
    |     3 |     2 |           OLD2 |
    |     3 |     3 |           NEW2 |
    |     4 |     1 |          [PAR] |
    |     4 |     2 |           OLD3 |
    |     4 |     3 |           NEW4 |
    |     5 |     1 |          [SEX] |
    |     5 |     2 |           OLD4 |
    |     5 |     3 |           NEW5 |
    |     6 |     1 |          [AGE] |
    |     6 |     2 |           OLD5 |
    |     6 |     3 |           NEW6 |
    |     7 |     1 |    [REIN TYPE] |
    |     7 |     2 |           OLD6 |
    |     7 |     3 |           NEW7 |
    |     8 |     1 |         [RTT ] |
    |     8 |     2 |           OLD7 |
    |     8 |     3 |           NEW8 |
    |     9 |     1 |      [REIN CO] |
    |     9 |     2 |           OLD9 |
    |     9 |     3 |           NEW9 |
    |    10 |     1 | [PREM DUR OFF] |
    |    10 |     2 |          OLD10 |
    |    10 |     3 |          NEW10 |



  [1]: http://sqlfiddle.com/#!3/eb9d3/21

  [2]: http://sqlfiddle.com/#!3/eb9d3/21/0

  [3]: http://sqlfiddle.com/#!3/eb9d3/21/1

  [4]: http://sqlfiddle.com/#!3/eb9d3/21/2

Open in new window

0
 

Author Comment

by:BeyondBGCM
Comment Utility
I really liked your examples , what if a field appers more than once , for an example , if RS appears 2 times with [RS ], New2 and New44
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>" what if a field appears more than once"

you could test that for yourself, but the way PIVOT works is that it requires the use of an aggregate function. Because we are dealing with strings MAX() is used (MIN() is an alternative), so what will happen is that the MAX(oldvalue) and MAX(newvalue) will be displayed under the column.

e.g. for these original rows

INSERT INTO Table1
      ([fieldname], [oldvalue], [newvalue])
...
      ('[SEX]', 'OLD4', 'NEW55'), --<< max newvalue
      ('[SEX]', 'OLD44', 'NEW5'), --<< max oldvalue

| ROWNO | PLAN |   RS | SMOKER |  PAR |   SEX |  AGE | REIN TYPE | RTT  | REIN CO | PREM DUR OFF |
|-------|------|------|--------|------|-------|------|-----------|------|---------|--------------|
|     1 | OLD1 | OLD2 |   OLD2 | OLD3 | OLD44 | OLD5 |      OLD6 | OLD7 |    OLD9 |        OLD10 |
|     2 | NEW1 | NEW2 |   NEW2 | NEW4 | NEW55 | NEW6 |      NEW7 | NEW8 |    NEW9 |        NEW10 |

Open in new window


BUT: This would not happen with the numeric heading approach I used. Instead you would get 2 columns, 1 for each row with fieldname = '[SEX]'
| ROWNO |      1 |    2 |        3 |     4 |     5 |     6 |     7 |           8 |      9 |        10 |
|-------|--------|------|----------|-------|-------|-------|-------|-------------|--------|-----------|
|     1 | [PLAN] | [RS] | [SMOKER] | [PAR] | [SEX] | [SEX] | [AGE] | [REIN TYPE] | [RTT ] | [REIN CO] |
|     2 |   OLD1 | OLD2 |     OLD2 |  OLD3 |  OLD4 | OLD44 |  OLD5 |        OLD6 |   OLD7 |      OLD9 |
|     3 |   NEW1 | NEW2 |     NEW2 |  NEW4 | NEW55 |  NEW5 |  NEW6 |        NEW7 |   NEW8 |      NEW9 |

Open in new window


see: http://sqlfiddle.com/#!3/39860b/1
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Have you tried my solution?
0
 

Author Comment

by:BeyondBGCM
Comment Utility
yes i tried your solution , yet , how can i make old4 and old44 appear both under the [Sex] column
0
 

Author Comment

by:BeyondBGCM
Comment Utility
i got that , it is using max , but there should be an alternate to use the above field to see both the values
0
 

Author Comment

by:BeyondBGCM
Comment Utility
and other columns which dont have any value for that place can display null ie if sex field has only 2 old values , but RS field has 4 old values , then Sex field can display null in place of that.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
I don't understand why you want old4 and old44 in the same column when they belong to different rows. From the example you posted all you wanted was to turn the rows into columns, which my code does.

Here is the code to deal with values that are not varchar and with NULL values, if the case:
-- drop table #tmp
create table #tmp(fieldname varchar(25),oldvalue varchar(50), newvalue varchar(50))
insert into #tmp
select 'PLAN', 'old1','new1' union all
select 'RS', 'old2','new2' union all
select 'SMOKER', 'old3','new3' union all
select 'PAR', 'old4','new4' union all
select 'SEX', 'old5','new5'
-- ...

select * from #tmp

declare
	@sql_fieldname varchar(max)='',
	@sql_olds varchar(max)='',
	@sql_news varchar(max)='',
	@sql_final varchar(max)

select 
	@sql_fieldname=@sql_fieldname+''''+isnull(cast(fieldname as varchar(100)),'')+''',',
	@sql_olds=@sql_olds++''''+isnull(cast(oldvalue as varchar(100)),'')+''',',
	@sql_news=@sql_news++''''+isnull(cast(newvalue as varchar(100)),'')+''','
from 
	#tmp
select 
	@sql_final=	'select '+replace(@sql_fieldname+'@#$',',@#$','')+' UNION ALL '+
				'select '+replace(@sql_olds+'@#$',',@#$','')+' UNION ALL '+
				'select '+replace(@sql_news+'@#$',',@#$','')

exec(@sql_final)

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Here is the code with the result using all the values you posted in your question. Let me know what exactly is not right:
-- drop table #tmp
create table #tmp(fieldname varchar(25),oldvalue varchar(50), newvalue varchar(50))
insert into #tmp
select '[PLAN]',      'OLD1',      'NEW1' UNION ALL
select '[RS]',      'OLD2',      'NEW2' UNION ALL
select '[SMOKER]',      'OLD2',      'NEW2' UNION ALL
select '[PAR]',      'OLD3',      'NEW4' UNION ALL
select '[SEX]',      'OLD4',      'NEW5' UNION ALL
select '[AGE]',      'OLD5',      'NEW6' UNION ALL
select '[REIN TYPE]',      'OLD6',      'NEW7' UNION ALL
select '[RTT]',      'OLD7',      'NEW8' UNION ALL
select '[REIN CO]',      'OLD9',      'NEW9' UNION ALL
select '[PREM DUR OFF]',      'OLD10',      'NEW10' UNION ALL
select '[CESS]',      'OLD11',      'NEW11' UNION ALL
select '[EFF-DATE]',      'OLD12',      'NEW12' UNION ALL
select '[PD-TO-DT]',      'OLD13',      'NEW13' UNION ALL
select '[ME-DATE]',      'OLD14',      'NEW14' UNION ALL
select '[ORG-CESS-AMT]',      'OLD15',      'NEW15' UNION ALL
select '[BASE-TTY]',      'OLD16',      'NEW16' UNION ALL
select '[WP-FACE-AMT]',      'OLD17',      'NEW17' UNION ALL
select '[WP-TTY]',      'OLD18',      'NEW18' UNION ALL
select '[AD-FACE-AMT]',      'OLD19',      'NEW19' UNION ALL
select '[AD-TTY]',      'OLD20',      'NEW20' UNION ALL
select '[FLAT-EX]',      'OLD21',      'NEW21' UNION ALL
select '[FLAT-EX-TTY]',      'OLD22',      'NEW22' UNION ALL
select '[RETEN]',      'OLD23',      'NEW23' UNION ALL
select '[BILL ADJ FLG]',      'OLD',      'NEW25' UNION ALL
select '[BILL-AMOUNT]',      'OLD7',      'NEW26' UNION ALL
select '[BILL-ALLOW]',      'OLD7',      'NEW27' UNION ALL
select '[REST PREM]',      'OLD7',      'NEW28' UNION ALL
select '[TOT-PREM]',      'OLD7',      'NEW29' UNION ALL
select '[TOT-PREM]',      'OLD7',      'NEW30' UNION ALL
select '[CESS-AMT]',      'OLD7',      'NEW31' UNION ALL
select '[DECR]',      'OLD7',      'NEW32' UNION ALL
select '[LIFE-PREM]',      'OLD7',      'NEW33' UNION ALL
select '[POL-FEE]',      'OLD7',      'NEW34' UNION ALL
select '[AD-PREM]',      'OLD7',      'NEW35' UNION ALL
select '[WP-PREM]',      'OLD7',      'NEW36' UNION ALL
select '[EXTRA-PREM]',      'OLD7',      'NEW37' UNION ALL
select '[ALLOWANCE]',      'OLD7',      'NEW38' UNION ALL
select '[NOTIFY-DT]',      'OLD7',      'NEW39' UNION ALL
select '[RSN]',      'OLD7',      'NEW40' UNION ALL
select '[STAT]',      'OLD7',      'NEW41' UNION ALL
select '[PREV STAT]',      'OLD42',      'NEW7' UNION ALL
select '[YTD GROS PRM]',      'OLD43',      'NEW7' UNION ALL
select '[MULT PREM]',      'OLD44',      'NEW7' UNION ALL
select '[ALLOW EXCESS]',      'OLD45',      'NEW7'


select * from #tmp

declare
	@sql_fieldname varchar(max)='',
	@sql_olds varchar(max)='',
	@sql_news varchar(max)='',
	@sql_final varchar(max)

select 
	@sql_fieldname=@sql_fieldname+''''+isnull(cast(fieldname as varchar(100)),'')+''',',
	@sql_olds=@sql_olds++''''+isnull(cast(oldvalue as varchar(100)),'')+''',',
	@sql_news=@sql_news++''''+isnull(cast(newvalue as varchar(100)),'')+''','
from 
	#tmp
select 
	@sql_final=	'select '+replace(@sql_fieldname+'@#$',',@#$','')+' UNION ALL '+
				'select '+replace(@sql_olds+'@#$',',@#$','')+' UNION ALL '+
				'select '+replace(@sql_news+'@#$',',@#$','')

exec(@sql_final)
GO

-- RESULT:
[PLAN] [RS] [SMOKER] [PAR] [SEX] [AGE] [REIN TYPE] [RTT] [REIN CO] [PREM DUR OFF] [CESS] [EFF-DATE] [PD-TO-DT] [ME-DATE] [ORG-CESS-AMT] [BASE-TTY] [WP-FACE-AMT] [WP-TTY] [AD-FACE-AMT] [AD-TTY] [FLAT-EX] [FLAT-EX-TTY] [RETEN] [BILL ADJ FLG] [BILL-AMOUNT] [BILL-ALLOW] [REST PREM] [TOT-PREM] [TOT-PREM] [CESS-AMT] [DECR] [LIFE-PREM] [POL-FEE] [AD-PREM] [WP-PREM] [EXTRA-PREM] [ALLOWANCE] [NOTIFY-DT] [RSN] [STAT] [PREV STAT] [YTD GROS PRM] [MULT PREM] [ALLOW EXCESS]
OLD1   OLD2 OLD2     OLD3  OLD4  OLD5  OLD6        OLD7  OLD9      OLD10          OLD11  OLD12      OLD13      OLD14     OLD15          OLD16      OLD17         OLD18    OLD19         OLD20    OLD21     OLD22         OLD23   OLD            OLD7          OLD7         OLD7        OLD7       OLD7       OLD7       OLD7   OLD7        OLD7      OLD7      OLD7      OLD7         OLD7        OLD7        OLD7  OLD7   OLD42       OLD43          OLD44       OLD45
NEW1   NEW2 NEW2     NEW4  NEW5  NEW6  NEW7        NEW8  NEW9      NEW10          NEW11  NEW12      NEW13      NEW14     NEW15          NEW16      NEW17         NEW18    NEW19         NEW20    NEW21     NEW22         NEW23   NEW25          NEW26         NEW27        NEW28       NEW29      NEW30      NEW31      NEW32  NEW33       NEW34     NEW35     NEW36     NEW37        NEW38       NEW39       NEW40 NEW41  NEW7        NEW7           NEW7        NEW7

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
using Zberteoc's code immediately above on my smaller sample set results in:

| COLUMN_0 | COLUMN_1 | COLUMN_2 | COLUMN_3 | COLUMN_4 | COLUMN_5 | COLUMN_6 |    COLUMN_7 | COLUMN_8 |  COLUMN_9 |      COLUMN_10 |
|----------|----------|----------|----------|----------|----------|----------|-------------|----------|-----------|----------------|
|   [PLAN] |     [RS] | [SMOKER] |    [PAR] |    [SEX] |    [SEX] |    [AGE] | [REIN TYPE] |   [RTT ] | [REIN CO] | [PREM DUR OFF] |
|     OLD1 |     OLD2 |     OLD2 |     OLD3 |     OLD4 |    OLD44 |     OLD5 |        OLD6 |     OLD7 |      OLD9 |          OLD10 |
|     NEW1 |     NEW2 |     NEW2 |     NEW4 |    NEW55 |     NEW5 |     NEW6 |        NEW7 |     NEW8 |      NEW9 |          NEW10 |

Open in new window


so, like my approach you get 1 column for each of the 2 original rows

(ps I had assumed the dynamic sql approach was to provide dynamic column headings but that isn't the case it seems.)

+edit: that's not a criticism, just an observation. If you have a variable number of columns then dynamic code of some sort is required.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:BeyondBGCM
Comment Utility
I think i need to add one more thing here , that this table is to show history data of each column , ie whatever values are changed for that column in one month or one year .

so only showing 2 rows is not a solution , as one column may have 5 history rows, another may have 20 , and other may have no change .

so it is right that i have to convert rows in to columns, but that column will have multiple occurances , like , it may have [RS]  appearing 10 times . and [Gender] appearing only 5 times ........
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
It is not 'right' in my view to flip this information into columns, and expect infinite columns with variable headings ....... using SQL

but I doubt my opinion in this will have much impact
0
 

Author Comment

by:BeyondBGCM
Comment Utility
[PLAN]      [RS]      [RS]      [SMOKER]       [PAR]      
OLD1      OLD2      OLD22      OLD2      OLD3      OLD4      
NEW1      NEW2      NEW22      NEW2      NEW4      NEW5

In above example , i need one query which can provide  [RS]  in following way

[PLAN]      [RS]      [SMOKER]       [PAR]      
OLD1      OLD2      OLD2            OLD4      
NEW1      NEW2      NEW2            NEW5
null            OLD22       null                                 null
null           New22       null                                  null


can you please suggest ...... its important .....
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
and 3 changes would be:

[PLAN]      [RS]      [SMOKER]       [PAR]      
OLD1      OLD2      OLD2            OLD4      
NEW1      NEW2      NEW2            NEW5
null            OLD22       null                                 null
null           New22       null                                  null
null            OLD22-3       null                                 null
null           New22-3       null                                  null

and 4

[PLAN]      [RS]      [SMOKER]       [PAR]      
OLD1      OLD2      OLD2            OLD4      
NEW1      NEW2      NEW2            NEW5
null            OLD22       null                                 null
null           New22       null                                  null
null            OLD22-3       null                                 null
null           New22-3       null                                  null
null            OLD22-4       null                                 null
null           New22-4       null                                  null

and it keeps adding for every addition change?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
In this audit data do you have any datetime information? (I really hope you do)

can you explain how we determine the row sequence?
(e.g. by a datetime field)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
To produce this I used  CTE using row_number() to figure out how to do the presentation of multiple "sets of rows"

| ROWNO |   PLAN |     RS | SMOKER |    PAR |   SEX |    AGE | REIN TYPE |    RTT | REIN CO | PREM DUR OFF |
|-------|--------|--------|--------|--------|-------|--------|-----------|--------|---------|--------------|
|     1 |   OLD1 |   OLD2 |   OLD2 |   OLD3 |  OLD4 |   OLD5 |      OLD6 |   OLD7 |    OLD9 |        OLD10 |
|     2 |   NEW1 |   NEW2 |   NEW2 |   NEW4 | NEW55 |   NEW6 |      NEW7 |   NEW8 |    NEW9 |        NEW10 |
|     1 | (null) | (null) | (null) | (null) | OLD44 | (null) |    (null) | (null) |  (null) |       (null) |
|     2 | (null) | (null) | (null) | (null) |  NEW5 | (null) |    (null) | (null) |  (null) |       (null) |

Open in new window


This is very ugly, but might help you (maybe)

it is NOT dynamic in any sense, you have to know the columns in advance, and it simply repeats the query to union the result, you would need an added "layer" for a third possible set of date etc.

;with CTE as (
  select *, row_number() over(partition by fieldname order by (select 1)) as rn
  from table1
  )

SELECT rowno, [PLAN], [RS], [SMOKER], [PAR], [SEX], [AGE], [REIN TYPE], [RTT], [REIN CO], [PREM DUR OFF]
FROM (
      select ca.fieldname, ca.rowno, ca.value
      from (
             select
                    rtrim(replace(replace([fieldname],'[',''),']','')) as fieldname
                  , [oldvalue]
                  , [newvalue]
             from CTE
             where rn=1
           ) x
      cross apply (
                  values  
                          (fieldname,1,[oldvalue] ),
                          (fieldname,2,[newvalue] )
                  ) ca (fieldname, rowno, value)  
     )  AS SourceTable
PIVOT
    (
      MAX(value)
      FOR fieldname IN ([PLAN], [RS], [SMOKER], [PAR], [SEX], [AGE], [REIN TYPE], [RTT], [REIN CO], [PREM DUR OFF])
    ) AS PivotTable

UNION ALL

SELECT rowno, [PLAN], [RS], [SMOKER], [PAR], [SEX], [AGE], [REIN TYPE], [RTT], [REIN CO], [PREM DUR OFF]
FROM (
      select ca.fieldname, ca.rowno, ca.value
      from (
             select
                    rtrim(replace(replace([fieldname],'[',''),']','')) as fieldname
                  , [oldvalue]
                  , [newvalue]
             from CTE
             where rn=2
           ) x
      cross apply (
                  values  
                          (fieldname,1,[oldvalue] ),
                          (fieldname,2,[newvalue] )
                  ) ca (fieldname, rowno, value)  
     )  AS SourceTable
PIVOT
    (
      MAX(value)
      FOR fieldname IN ([PLAN], [RS], [SMOKER], [PAR], [SEX], [AGE], [REIN TYPE], [RTT], [REIN CO], [PREM DUR OFF])
    ) AS PivotTable

Open in new window


http://sqlfiddle.com/#!3/39860b/28
0
 

Author Comment

by:BeyondBGCM
Comment Utility
yes  , there is a date column in the table , and  the example set you have shown is correct, we will have audit data increasing for various columns we add.
0
 

Author Comment

by:BeyondBGCM
Comment Utility
the query looks good, but we need to avoid this many union all , it can have many rows for each field , is there any better and cool solution
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Can you provide a representative sample that includes the date field please (also give us the table name)

Do the older records belong at the top?
 or, the bottom?

Is it necessary to dynamically create the columns?     (looks like example a)
 or, do you know what the column headings are in advance?     (looks like example a)
 or, can you live with column headings such as 1 2 3 4 5 6    (looks like example b)

a
| ROWNO |   PLAN |     RS | SMOKER |    PAR |   SEX |    AGE | REIN TYPE |    RTT | REIN CO | PREM DUR OFF |

Open in new window

b
| ROWNO |      1 |    2 |        3 |     4 |     5 |     6 |           7 |      8 |         9 |             10 |
|-------|--------|------|----------|-------|-------|-------|-------------|--------|-----------|----------------|
|     1 | [PLAN] | [RS] | [SMOKER] | [PAR] | [SEX] | [AGE] | [REIN TYPE] | [RTT ] | [REIN CO] | [PREM DUR OFF] |
|     2 |   OLD1 | OLD2 |     OLD2 |  OLD3 |  OLD4 |  OLD5 |        OLD6 |   OLD7 |      OLD9 |          OLD10 |
|     3 |   NEW1 | NEW2 |     NEW2 |  NEW4 |  NEW5 |  NEW6 |        NEW7 |   NEW8 |      NEW9 |          NEW10 |

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"Can you provide a representative sample that includes the date field please (also give us the table name)"

in a format that can be used for inserts e.g. tab separated text, CSV, spreadsheet, actual insert script

nb: the text data in the question does not parse easily
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
This is the best I can do at the moment, only rudimentary testing and I would reduce the number of columns from 100
SELECT
     rowgrp, subrow, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61], [62], [63], [64], [65], [66], [67], [68], [69], [70], [71], [72], [73], [74], [75], [76], [77], [78], [79], [80], [81], [82], [83], [84], [85], [86], [87], [88], [89], [90], [91], [92], [93], [94], [95], [96], [97], [98], [99], [100]
FROM (
      select colgrp, x.rowgrp, ca.subrow, ca.value
      from (
             select
                    fieldname
                  , oldvalue
                  , newvalue
                  , dense_rank() over(                       order by fieldname) as colgrp 
                  , row_number() over(partition by fieldname order by dt DESC  ) as rowgrp 
             from tblXYZ_Audit
           ) x
      cross apply (
                  values  
                          (0,[fieldname] ),
                          (1,[oldvalue] ),
                          (2,[newvalue] )
                  ) ca (subrow, value)  
     )  AS SourceTable
PIVOT
    (
      MAX(value)
      FOR colgrp IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31], [32], [33], [34], [35], [36], [37], [38], [39], [40], [41], [42], [43], [44], [45], [46], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61], [62], [63], [64], [65], [66], [67], [68], [69], [70], [71], [72], [73], [74], [75], [76], [77], [78], [79], [80], [81], [82], [83], [84], [85], [86], [87], [88], [89], [90], [91], [92], [93], [94], [95], [96], [97], [98], [99], [100])
    ) AS PivotTable
where
      (rowgrp = 1 and subrow = 0) or subrow > 0
order by
    rowgrp, subrow
;

Open in new window


| ROWGRP | SUBROW |             1 |              2 |         3 |     4 |              5 |              6 |          7 |              8 |              9 |             10 |             11 |     12 |             13 |         14 |             15 |            16 |        17 |            18 |        19 |             20 |            21 |             22 |    23 |         24 |     25 |           26 |             27 |             28 |        29 |          30 |             31 |      32 |   33 |         34 |     35 |    36 |       37 |        38 |             39 |            40 |             41 |       42 |             43 |     44 |     45 |     46 |     47 |     48 |     49 |     50 |     51 |     52 |     53 |     54 |     55 |     56 |     57 |
|--------|--------|---------------|----------------|-----------|-------|----------------|----------------|------------|----------------|----------------|----------------|----------------|--------|----------------|------------|----------------|---------------|-----------|---------------|-----------|----------------|---------------|----------------|-------|------------|--------|--------------|----------------|----------------|-----------|-------------|----------------|---------|------|------------|--------|-------|----------|-----------|----------------|---------------|----------------|----------|----------------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
|      1 |      0 | [AD-FACE-AMT] | [AD-PREM     ] | [AD-TTY ] | [AGE] | [ALLOW EXCESS] | [ALLOWANCE   ] | [BASE-TTY] | [BILL ADJ FLG] | [BILL-ALLOW  ] | [BILL-AMOUNT ] | [CESS-AMT    ] | [CESS] | [DECR        ] | [EFF-DATE] | [EXTRA-PREM  ] | [FLAT-EX-TTY] | [FLAT-EX] | [LIFE-PREM  ] | [ME-DATE] | [MULT PREM   ] | [NOTIFY-DT  ] | [ORG-CESS-AMT] | [PAR] | [PD-TO-DT] | [PLAN] | [POL-FEE   ] | [PREM DUR OFF] | [PREV STAT   ] | [REIN CO] | [REIN TYPE] | [REST PREM   ] | [RETEN] | [RS] | [RSN     ] | [RTT ] | [SEX] | [SMOKER] | [STAT   ] | [TOT-PREM    ] | [WP-FACE-AMT] | [WP-PREM     ] | [WP-TTY] | [YTD GROS PRM] | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      1 |      1 |         OLD19 |           OLD7 |     OLD20 |  OLD5 |          OLD45 |           OLD7 |      OLD16 |            OLD |           OLD7 |           OLD7 |           OLD7 |  OLD11 |           OLD7 |      OLD12 |           OLD7 |         OLD22 |     OLD21 |          OLD7 |     OLD14 |          OLD44 |          OLD7 |          OLD15 |  OLD3 |      OLD13 |   OLD1 |         OLD7 |          OLD10 |          OLD42 |      OLD9 |        OLD6 |           OLD7 |   OLD23 | OLD2 |       OLD7 |   OLD7 |  OLD4 |     OLD2 |      OLD7 |           OLD7 |         OLD17 |           OLD7 |    OLD18 |          OLD43 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      1 |      2 |         NEW19 |          NEW35 |     NEW20 |  NEW6 |           NEW7 |          NEW38 |      NEW16 |          NEW25 |          NEW27 |          NEW26 |          NEW31 |  NEW11 |          NEW32 |      NEW12 |          NEW37 |         NEW22 |     NEW21 |         NEW33 |     NEW14 |           NEW7 |         NEW39 |          NEW15 |  NEW4 |      NEW13 |   NEW1 |        NEW34 |          NEW10 |           NEW7 |      NEW9 |        NEW7 |          NEW28 |   NEW23 | NEW2 |      NEW40 |   NEW8 |  NEW5 |     NEW2 |     NEW41 |          NEW29 |         NEW17 |          NEW36 |    NEW18 |           NEW7 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      2 |      1 |        (null) |         (null) |    (null) |  OLD5 |         (null) |         (null) |     (null) |         (null) |         (null) |         (null) |         (null) |  OLD11 |         (null) |      OLD12 |         (null) |        (null) |    (null) |        (null) |    (null) |         (null) |        (null) |         (null) |  OLD3 |      OLD13 |   OLD1 |       (null) |          OLD10 |         (null) |      OLD9 |        OLD6 |         (null) |  (null) | OLD2 |     (null) |   OLD7 |  OLD4 |     OLD2 |    (null) |           OLD7 |        (null) |         (null) |   (null) |         (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
|      2 |      2 |        (null) |         (null) |    (null) |  NEW6 |         (null) |         (null) |     (null) |         (null) |         (null) |         (null) |         (null) |  NEW11 |         (null) |      NEW12 |         (null) |        (null) |    (null) |        (null) |    (null) |         (null) |        (null) |         (null) |  NEW4 |      NEW13 |   NEW1 |       (null) |          NEW10 |         (null) |      NEW9 |        NEW7 |         (null) |  (null) | NEW2 |     (null) |   NEW8 |  NEW5 |     NEW2 |    (null) |          NEW30 |        (null) |         (null) |   (null) |         (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

Open in new window


http://sqlfiddle.com/#!3/39860b/52
0
 

Author Comment

by:BeyondBGCM
Comment Utility
1. the data i shared in question , is the only data i have at this moment , and i appriciate the solution you have given with CTE , only thing is , the multiple union is not a standard solution .

it can be done in much simpler way .

2. Apart from that , i have few more questions . in the same query
  a. SELECT rowno, [REIN TYPE], [RTT], [TOT-PREM], [PREM DUR OFF]
FROM (
      select ca.fieldname, ca.rowno, ca.value
      from (
             select
                    rtrim(replace(replace([fieldname],'[',''),']','')) as fieldname
                  , [oldvalue]
                  , [newvalue]
             from #CTE
             where rn=@index
           ) x
      cross apply (
                  values  
                          (fieldname,1,[oldvalue] ),
                          (fieldname,2,[newvalue] )
                  ) ca (fieldname, rowno, value)  
     )  AS SourceTable

in above query , how we reached to the step till cross apply , i mean can you please explain the logic for the same.
0
 

Author Comment

by:BeyondBGCM
Comment Utility
if it can be explained in parts , it will be great.
0
 

Author Comment

by:BeyondBGCM
Comment Utility
YOU ARE THE BEST SQL USER I MET TILL DATE , YOUR LAST QUERY MADE ME SAY WOW, ........we can still make it better , are you from Microsoft ....?
0
 

Author Comment

by:BeyondBGCM
Comment Utility
sorry for caps ,
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
I don't use CTEs unless I have to, or there is some compelling reason for it. That was a temporary approach until I heard back from you about a couple of things.

The data in the question does not contain dates NOR does it contain any "repetition" & it wasn't simple to get that data into a table. I did end up using that data, but really supply of data is something you should do. If you add further fields during conversation (i.e. that date field) then you should add that new data too. For SQL questions having reasonable data makes such a difference to answering.

-----------------
explanation. I'll try.

The most important part is the inner most query where I use a CROSS APPLY
This is actually an UNPIVOT query so that all the data I need for a subsequent PIVOT is in one consistent row structure. Here is also where I calculate both the column reference (colgrp) and a row group (rowgrp).

      select x.colgrp, x.rowgrp, ca.subrow, ca.value
      from (
             select  fieldname , oldvalue , newvalue
                  , dense_rank() over(                       order by fieldname) as colgrp 
                  , row_number() over(partition by fieldname order by dt DESC  ) as rowgrp 
             from tblXYZ_Audit
           ) x
      cross apply (  values  
                              (0,[fieldname] ),
                              (1,[oldvalue] ),
                              (2,[newvalue] )
                  ) ca (subrow, value)  

Open in new window


The column reference is a number based on dense_rank() ordered by fieldname. This is used so that the same column reference is calculated for any source row sharing a fieldname value. Ultimately it is through this number that the old/new pairs are aligned under the relevant fieldname. I HAVEN'T REALLY TESTED THIS BIT, BUT SHOULD WORK (I have done similar things using DENSE_RANK() before)

Note that for a single source table row, this UNPIVOT subquery will produce 3 rows, this occurs because of the values operator inside the cross apply and then the 3 subrows you see in that

e.g. a source row of
[AD-FACE-AMT] OLD19 NEW19

becomes

x.colgrp, x.rowgrp, ca.subrow, ca.value
1              1               0                   [AD-FACE-AMT]
1              1               1                   OLD19
1              1               2                   NEW19

any subsequent (older) data for that field rowgrp increases by 1 and would be something like

x.colgrp, x.rowgrp, ca.subrow, ca.value
1              2               0                   [AD-FACE-AMT]  --<< this row gets filtered out later as it isn't required in final output
1              2               1                   OLD19
1              2               2                   NEW19

Once this unpivot subquery is completed it is just a very standard pivot that gets applied. Not much to be said for that part.

i.e.
because all those rows share the colgrp value 1, they are arranged into a single column
with the row sequence then determined by rowgrp and subrow
plus we filter out some unwanted rows. and that data you see above becomes

x.rowgrp, ca.subrow, [1] --<< colgrp is used to determine the columns
1               0                   [AD-FACE-AMT]
1               1                   OLD19
1               2                   NEW19
2               1                   OLD19
2               2                   NEW19
0
 

Author Closing Comment

by:BeyondBGCM
Comment Utility
the best answer i received to my queries in last one year
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
query help 18 50
using & in TSQL 18 21
Help with SQL Query 23 39
Delete Trigger in SQL Server2008R2 5 0
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video discusses moving either the default database or any database to a new volume.
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…

744 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

12 Experts available now in Live!

Get 1:1 Help Now