Click here to Skip to main content
15,890,512 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
name   pdcode    amount
 1     20       5
 1     30       10 
 2     20       199
 3     30       40

If you see above pdcode=30 is not available for name =2 and similarily pdcode=20 not available for name=3 . I need output as below
name   pdcode    amount
 1     20       5
 1     30       10 
 2     20       199
 2     30        0
 3     30       40
 3     20        0
Posted
Updated 2-Jun-15 6:02am
v2
Comments
Andy Lanng 2-Jun-15 11:47am    
Homework?

Even then, it's not clear what you need

If you are saying that every value of [name] must have an entry for all possible values of [pdcode] then...

1. Determine what all the possible combinations of [name] and [pdcode] are required. If you assume that the table contains at least one record for each [name] AND at least one record for each [pdcode] you can do this by selecting the DISTINCT[^] values of each and using CROSS APPLY[^]

2. You can put those results into a Common Table Expression (CTE) - see Common Table Expressions(CTE) in SQL SERVER 2008[^]

3. If you use the results from the CTE as the left table (i.e. what we want) and LEFT OUTER JOIN[^] to what we already have on the table you can filter the results for where pdcode from the original table is NULL - i.e. the list of "missing" rows

4. You can then just INSERT INTO[^] those values into the original table with a default value for the amount

It's a lot simpler than it sounds...
- Create Test data:
SQL
create table joemens
(
    [name] int,
    pdcode int,
    amount int
)

insert into joemens values
(1,20,5),
(1,30,10),
(2, 20, 199),
(3,30,40)

- Identify all the values we need
SQL
SELECT * FROM
(select distinct pdcode from joemens) A
CROSS APPLY (select distinct [name] from joemens) B
gives results of
pdcode name
20     1
20     2
20     3
30     1
30     2
30     3

- Stick it into a CTE and identify the gaps
SQL
;WITH CTE AS(
    SELECT * FROM
    (select distinct pdcode from joemens) A
    CROSS APPLY (select distinct [name] from joemens) B
)
select * -- CTE.[Name], CTE.pdcode, 0
from CTE
left outer join joemens J on J.[name]=CTE.[name] AND J.pdcode=CTE.pdcode
which results in
20	1	1	20	5
20	2	2	20	199
20	3	NULL	NULL	NULL
30	1	1	30	10
30	2	NULL	NULL	NULL
30	3	3	30	40

- pull it all together - we want to insert the rows where the NULLs appear
SQL
;WITH CTE AS(
    SELECT * FROM
    (select distinct pdcode from joemens) A
    CROSS APPLY (select distinct [name] from joemens) B
)
INSERT INTO joemens
select CTE.[Name], CTE.pdcode, 0
from CTE
left outer join joemens J on J.[name]=CTE.[name] AND J.pdcode=CTE.pdcode
WHERE J.pdcode IS NULL
 
Share this answer
 
Comments
Peter Leow 2-Jun-15 12:41pm    
Very thorough explanation and illustration. 5ed!
A combination of:
1. Common Table Expressions(CTE) in SQL SERVER 2008[^]
2. Visual Representation of SQL Joins[^]
leading to:
SQL
With CTE1(pdcode)
AS
(
SELECT pdcode FROM tablename
),
CTE2(name, pdcode)
AS
(
SELECT DISTINCT t1.name, CTE1.pdcode FROM CTE1, tablename t1
)
SELECT CTE2.name, CTE2.pdcode, ISNULL(t2.amount, 0)  FROM CTE2
LEFT JOIN tablename t2 on CTE2.name=t2.name and CTE2.pdcode=t2.pdcode
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900