use distinct or exist statement with multiple select? 2

[radiance]

Hi.

i need to show a set of records based on the programs in the system.

some of the programs are listed as duplicates, so i still need to show the actual program and the values for it;

I don't think using distinct will be the answer, since I still need to show all values.
ie:

PROGRAM VALIDSTREAM CHARGE
katmandu yes br01
katmandu no br02
katmandu yes ser05
taiwan yes sfare06


how can i write my sql, so that I can get the following results displayed...same table, no joins (i am not authorized to change the view either)

br01 br02 ser05 sfare06 siflt
katmandu yes no yes 0 0
taiwan 0 0 0 yes 0


thanks...

 

[SQLDenis]

Very simmilar to what Alex answered yesterday thread183-1345129


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[radiance]

sorry, that doesn't help me.

my view is : VIEW: dsp_FULLStream

sample columns are program, validStream, charge

where 'program' may be listed a few times, but not the 'charge'.

 

[SQLSister]

Well you could create a temp table with the columns you want (or table variable)
Insert the programs using the select distinct programname from table as the source of your insert data. As part of the insert, set all the other columns to a value of 0. Then create an update for each of the rest of the columns column by joining to the temp table with a where clause that picks out only the records with that charge. Finally select the results of your temp table.

Questions about posting. See faq183-874