pivot or cross tab assistance

Question

pivot or cross tab assistance

Rich96

SSC Eights!

Points: 947
More actions
June 16, 2009 at 1:35 pm

#135345

I'm struggling with the PIVOT function but I think it's not the function I need to be using. I've tried MERGE JOIN, MERGE, UNION ALL. I'm sure it's simple but confused as ever right now...
I have 2 tables (bear in mind I have no control over the DDL of these tables):
Table A

CIF ACCTNO SSNO
A123 123456 123456789
A123 123456 123456789
A124 123457 987654321
A125 765432 555555555
A125 765432 555555555
Table B:
CIF ACCTNO SSNO CODE
A123 123456 123456789 14
A123 123456 123456789 51
A124 123457 987654321 13
A125 765432 555555555 14
A125 765432 555555555 20
Desired results:
CIF ACCTNO SSNO CODE CODE1
A123 123456 123456789 14 51
A124 123457 987654321 13
A125 765432 555555555 14 20
I appreciate any and all comments.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi,

as far as I can see Table A is not relevant since it is identical to table B except for the addtl. code column in table B.

So I'm using table B only.

In order to number the occurrences of the codes I used a CTE together with Row_number function.

declare @t table(CIF char(4), ACCTNO int, SSNO varchar(10), CODE int)

insert into @t

select 'A123', 123456 , '123456789' , 14 union all

select 'A123', 123456 , '123456789' ,51 union all

select 'A124', 123457 , '987654321' , 13 union all

select 'A125', 765432 , '555555555' , 14 union all

select 'A125', 765432 , '555555555', 20

;WITH CTE_RowNum

AS(

SELECT cif as cif,

acctno as acctno,

ssno as ssno,

code as code,

ROW_NUMBER() OVER(Partition by cif ORDER BY cif,code) as row

FROM @t t)

--select * from CTE_RowNum

SELECT cif, acctno,ssno,[1] as code, [2] as code1

FROM (

SELECT cif, acctno , ssno, code,row

FROM CTE_RowNum

) p

PIVOT (

SUM (code)

FOR row IN

( [1], [2])

) AS pvt

/* result set

cifacctnossnocodecode1

A1231234561234567891451

A12412345798765432113NULL

A1257654325555555551420

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Rich96 SSC Eights! Points: 947 More actions · Answer 2

Rich96

SSC Eights!

Points: 947

June 16, 2009 at 2:44 pm

#1010500

Thanks so much.. that's going to do it for me!

LutzM SSC Guru Points: 107049 More actions · Answer 3

You're welcome!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Rich96 (6/16/2009)
Thanks so much.. that's going to do it for me!

Because this is a denormalization of data, I'm curious and I have to ask why this needs to be done? What are the business reasons for this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rich96 SSC Eights! Points: 947 More actions · Answer 5

-Jeff

Unfortunately I'm dealing with a 3rd party vendor that could not process the normalized table. In fact I've had to convert all my alphanumeric fields to ASCII..

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Rich96 (6/17/2009)
-Jeff
Unfortunately I'm dealing with a 3rd party vendor that could not process the normalized table. In fact I've had to convert all my alphanumeric fields to ASCII..

Heh... been there, done that. It's amazing that some of these 3rd parties can actually stay in business.

Thank you for taking the time to post the feedback, Rich. I appreciate it. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 7

will your output always only have code and code1

or is it possible to have:

code code1 code2 code3 etc?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Rich96 SSC Eights! Points: 947 More actions · Answer 8

I've actually added more "codeN" columns (5 total).. here is my final script -

declare @t table(CIF varchar(7), ACCTNO numeric(16,0), SSNO numeric(9,0), CODE int)

insert into @t

SELECT LN.CIFNO, LN.ACCTNO, CF.CFSSNO, LNS.LNSICD

FROM DB2_ODBC.JHASVR.DATSRM.LNMAST LN

LEFT JOIN DB2_ODBC.JHASVR.DATSRM.CFMAST CF

ON LN.CIFNO = CF.CFCIF#

LEFT JOIN DB2_ODBC.JHASVR.DATSRM.LNSICC LNS

ON LN.ACCTNO = LNS.ACCTNO

WHERE LN.STATUS NOT IN (2, 8) AND LN.ACTYPE NOT IN ('D', 'S') AND LN.TYPE 'ZA'

ORDER BY LN.CIFNO

;WITH CTE_RowNum

AS(

SELECT cif as cif,

acctno as acctno,

ssno as ssno,

code as code,

ROW_NUMBER() OVER(Partition by cif ORDER BY cif,code) as row

FROM @t t)

--select * from CTE_RowNum

INSERT INTO HFSCodeWorkTable

SELECT cif, acctno,ssno,[1] as code1, [2] as code2, [3] as code3, [4] as code4, [5] as code5

FROM (

SELECT cif, acctno , ssno, code,row

FROM CTE_RowNum

) p

PIVOT (

SUM (code)

FOR row IN

( [1], [2], [3], [4], [5])

) AS pvt

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

Have you tried making it dynamic?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

To add to those comments.... I don't know how much performance is important for this task, but an old fashioned pre-aggregated CrossTab can be quite a bit faster than a PIVOT. It's also easier to read, if that's important.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rich96 SSC Eights! Points: 947 More actions · Answer 11

I'd be willing to learn anything new 😀 if you are willing to throw a script my way. There potentially could be 29 codes per record but I chose to pass along the first 5.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 12

Taking the CTE and test data from Imu92 here is a dynamic solution

CREATE table t (CIF char(4), ACCTNO int, SSNO varchar(10), CODE int)

insert into t

select 'A123', 123456 , '123456789' , 14 union all

select 'A123', 123456 , '123456789' ,51 union all

select 'A124', 123457 , '987654321' , 13 union all

select 'A123', 123456 , '123456789' ,71 union all

select 'A124', 123457 , '987654321' , 18 union all

select 'A125', 765432 , '555555555' , 14 union all

select 'A125', 765432 , '555555555', 20

DECLARE @Colslist VARCHAR(MAX)

DECLARE @Cols TABLE (Head VARCHAR(MAX))

;WITH CTE_RowNum

AS(

SELECT cif as cif,

acctno as acctno,

ssno as ssno,

code as code,

ROW_NUMBER() OVER(Partition by cif ORDER BY cif,code) as row

FROM t)

INSERT @Cols (Head)

SELECT DISTINCT ROW FROM CTE_RowNum

SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head+ ']'

FROM @Cols t

SELECT @ColsList

EXEC ('SELECT *

FROM

(

SELECT cif as cif,

acctno as acctno,

ssno as ssno,

code as code,

ROW_NUMBER() OVER(Partition by cif ORDER BY cif,code) as row

FROM t

) t

PIVOT (SUM(code) FOR row IN (' + @ColsList + ')) PVT')

PLEASE NOTE , as Jeff mentioned you NEED to test this for performance as the old - cross tab style is most likely going to be faster. Have a look at Jeff's articles regarding Cross-tab performance.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Rich96 (6/17/2009)
I'd be willing to learn anything new 😀 if you are willing to throw a script my way. There potentially could be 29 codes per record but I chose to pass along the first 5.

The articles Christopher refers to are as follows:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/cross+tab/65048/

The first article describes and compares the Cross-Tab method and the Pivot method. It has the "race results" at the end of the article.

The second article explains how to build CrossTabs dynamically to handle a relatively unknown number of columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rich96 SSC Eights! Points: 947 More actions · Answer 14

Jeff & Christopher.. thanks so much for the feedback! Looks like I have some reading to do.