Trouble populating a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trouble populating a table

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-04 : 10:41:43

Hi,
My requirement is to populate a table (say table1)which is of the following structure

PLAN1,TYPECODE1,PLAN2,TYPECODE2,PLAN3,TYPECODE3...TILL 20

I have to get this data from another table where the data and structure is as below

PLAN TYPECODE SEQUENCE
---- -------- ---------
TESS1 TESSEN 1
TESS2 TELIT 2
TESS3 TELIM 3..AND SO ON TILL 20

How do i get to populate table1 as below
TESS1,TESSEN,TESS2,TELIT,TESS3,TELIM....SO ON TILL 20

Can you please advise what the best way to do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 10:51:17

[code]
SELECT MAX(CASE WHEN SEQUENCE =1 THEN PLAN ELSE NULL END) AS PLAN1,
MAX(CASE WHEN SEQUENCE =1 THEN TYPECODE ELSE NULL END) AS TYPECODE1,
MAX(CASE WHEN SEQUENCE =2 THEN PLAN ELSE NULL END) AS PLAN2,
MAX(CASE WHEN SEQUENCE =2 THEN TYPECODE ELSE NULL END) AS TYPECODE2,
MAX(CASE WHEN SEQUENCE =3 THEN PLAN ELSE NULL END) AS PLAN3,
MAX(CASE WHEN SEQUENCE =3 THEN TYPECODE ELSE NULL END) AS TYPECODE3,
....
MAX(CASE WHEN SEQUENCE =20 THEN PLAN ELSE NULL END) AS PLAN20,
MAX(CASE WHEN SEQUENCE =20 THEN TYPECODE ELSE NULL END) AS TYPECODE20,
FROM Table
[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-04 : 11:18:04

Thanks Visakh. This works alone , but I have to combine this with another table. Here is the whole statement.

SELECT B.LINE_OF_BUSINESS AS LOB,B.PRODUCT_CODE AS PRODUCT_CODE,
CASE WHEN A.BENEFIT_SEQ =1 THEN A.PLAN_CODE ELSE NULL END AS PLAN_CODE1,
CASE WHEN A.BENEFIT_SEQ =1 THEN A.BENEFIT_TYPE ELSE NULL END AS BENEFIT_TYPE1,
CASE WHEN A.BENEFIT_SEQ =2 THEN A.PLAN_CODE ELSE NULL END AS PLAN_CODE2,
CASE WHEN A.BENEFIT_SEQ =2 THEN A.BENEFIT_TYPE ELSE NULL END AS BENEFIT_TYPE2,
CASE WHEN A.BENEFIT_SEQ =3 THEN A.PLAN_CODE ELSE NULL END AS PLAN_CODE3,
CASE WHEN A.BENEFIT_SEQ =3 THEN A.BENEFIT_TYPE ELSE NULL END AS BENEFIT_TYPE3,
CASE WHEN A.BENEFIT_SEQ =4 THEN A.PLAN_CODE ELSE NULL END AS PLAN_CODE4,
CASE WHEN A.BENEFIT_SEQ =4 THEN A.BENEFIT_TYPE ELSE NULL END AS BENEFIT_TYPE4
FROM PPOLC AS B
INNER JOIN PPBEN AS A ON B.POLICY_NUMBER = A.POLICY_NUMBER AND B.COMPANY_CODE = A.COMPANY_CODE
WHERE A.POLICY_NUMBER = 'L0000092' AND A.COMPANY_CODE = '02'

PPOLC has only one row for that POLICY_NUMBER , but PPBEN has 4 rows for the same POLICY_NUMBER with different BENEFIT_SEQs.
When I run this , I get the output as below.
L ROPBTRM20 RTB20 BA NULL NULL NULL NULL NULL NULL
L ROPBTRM20 NULL NULL TERMPC OR NULL NULL NULL NULL
L ROPBTRM20 NULL NULL NULL NULL ROPB20 SU NULL NULL
L ROPBTRM20 NULL NULL NULL NULL NULL NULL LNB OR

How do i get just one row with all columns valued?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 11:57:32

you were close!


SELECT B.LINE_OF_BUSINESS AS LOB,B.PRODUCT_CODE AS PRODUCT_CODE, 
MAX(CASE WHEN A.BENEFIT_SEQ =1 THEN A.PLAN_CODE ELSE NULL END) AS PLAN_CODE1,
MAX(CASE WHEN A.BENEFIT_SEQ =1 THEN A.BENEFIT_TYPE ELSE NULL END) AS BENEFIT_TYPE1,
MAX(CASE WHEN A.BENEFIT_SEQ =2 THEN A.PLAN_CODE ELSE NULL END) AS PLAN_CODE2,
MAX(CASE WHEN A.BENEFIT_SEQ =2 THEN A.BENEFIT_TYPE ELSE NULL END) AS BENEFIT_TYPE2,
MAX(CASE WHEN A.BENEFIT_SEQ =3 THEN A.PLAN_CODE ELSE NULL END) AS PLAN_CODE3,
MAX(CASE WHEN A.BENEFIT_SEQ =3 THEN A.BENEFIT_TYPE ELSE NULL END) AS BENEFIT_TYPE3,
MAX(CASE WHEN A.BENEFIT_SEQ =4 THEN A.PLAN_CODE ELSE NULL END) AS PLAN_CODE4,
MAX(CASE WHEN A.BENEFIT_SEQ =4 THEN A.BENEFIT_TYPE ELSE NULL END) AS BENEFIT_TYPE4
FROM PPOLC AS B
INNER JOIN PPBEN AS A ON B.POLICY_NUMBER = A.POLICY_NUMBER AND B.COMPANY_CODE = A.COMPANY_CODE	
WHERE A.POLICY_NUMBER = 'L0000092' AND A.COMPANY_CODE = '02'
GROUP BY B.LINE_OF_BUSINESS,
B.PRODUCT_CODE

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-04 : 12:09:23

Perfect! Thanks a lot Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:10:30

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources