Query Problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Problem

Author

Topic

vbuser26
Starting Member

21 Posts

Posted - 2009-01-19 : 02:41:24

Hi Experts,
I have 7 rows of 100 value in A_COL, i need to make the value 673 to 7 rows also by adding 0 value in C_COL defend on B_COL missing value below is the sample table. Can anyone help me?
A_COL B_COL C_COL D_COL
100 50 21 780
100 56 22 780
100 62 185 780
100 68 184 780
100 74 184 780
100 80 164 780
100 86 20 780
673 62 92 368
673 68 92 368
673 74 92 368
673 80 92 368

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 03:17:35

so what should be desired output? all values as 0 for other fields?

vbuser26
Starting Member

21 Posts

Posted - 2009-01-19 : 03:27:58

Below should be the output
A_COL B_COL C_COL D_COL
100 50 21 780
100 56 22 780
100 62 185 780
100 68 184 780
100 74 184 780
100 80 164 780
100 86 20 780
673 50 0 368
673 56 0 368
673 62 92 368
673 68 92 368
673 74 92 368
673 80 92 368
673 86 0 368

Note: no using insert statement pls. ^_^ tnx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 09:46:57

[code]
SELECT tmp.A_COL,
tmp.B_COL,
COALESCE(t.C_COL,0) AS C_COL,
tmp.D_COL
FROM (
SELECT A_COL,B_COL,D_COL
FROM (SELECT DISTINCT A_COL,D_COL
FROM Table)a
CROSS JOIN(SELECT DISTINCT B_COL
FROM Table
)b
)tmp
LEFT JOIN Table t
ON tmp.A_COL=t.A_COL
AND tmp.B_COL=t.B_COL
[/code]

vbuser26
Starting Member

21 Posts

Posted - 2009-01-20 : 04:13:00

Thank you very much visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 09:08:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources