Problem with query... - 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 2005 Forums

Transact-SQL (2005)

Problem with query...

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2009-12-02 : 20:56:32

Hi Guys,
I am having a small problem with the query which I have written.
According to that query, it returns the following sample result set:

ID CODE NAME CONTACTPERSON AMOUNT
123 8214 Raaj KEVIN 200
123 8214 Raaj GEORGE 200
124 8000 Ram SHANE 100
128 9000 VIJ BOND 500
128 9000 VIJ STEVE 500

But What I actually want the result set to look like is in this way:

ID CODE NAME CONTACTPERSON AMOUNT
123 8214 Raaj KEVIN 200
GEORGE
124 8000 Ram SHANE 100
128 9000 VIJ BOND 500
STEVE

AS you can see in the above result set when ID,CODE,NAME and AMOUNT have same values, I would like to show only the CONTACTPERSON for the next row.

Any ideas of How to proceed?
Thanks,
Raaj.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-12-02 : 23:50:20

u can use ROW_NUMBER() function to achieve this, some thing like

SELECT
CASE WHEN Rno = 1 THEN ID ELSE NULL END AS ID,
CASE WHEN Rno = 1 THEN CODE ELSE NULL END AS CODE,
...
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, CODE, NAME ORDER BY ID, CODE, NAME) AS Rno
FROM tbl
) A

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

raaj
Posting Yak Master

129 Posts

Posted - 2009-12-03 : 19:07:52

Hi Peter,
Thanks for your reply.
I tried what you suggested. But its showing NULL values for the Duplicate rows.....
Here is the sample query which I have written:

Declare @Mytable Table (ID Int, Code Int, Name varchar(10), ContactPerson Varchar(10), Amount money)
Insert into @MyTable values(123, 8214, 'Raaj', 'Kevin', 200)
Insert into @MyTable values(123, 8214, 'Raaj', 'George', 200)
Insert into @MyTable values(124, 8000, 'Ram', 'Shane', 100)
Insert into @MyTable values(128, 9000, 'VIJ', 'BOND', 500)
Insert into @MyTable values(128, 9000, 'VIJ', 'STEVE', 500)

--Select * from @MyTable
SELECT
CASE WHEN Rno = 1 THEN ID ELSE NULL END AS ID,
CASE WHEN Rno = 1 THEN CODE ELSE NULL END AS CODE,
CASE WHEN Rno = 1 THEN NAME ELSE NULL END AS NAME,
CASE WHEN Rno = 1 THEN CONTACTPERSON ELSE NULL END AS CONTACTPERSON,
CASE WHEN Rno = 1 THEN AMOUNT ELSE NULL END AS AMOUNT

FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, CODE, NAME, AMOUNT ORDER BY ID, CODE, NAME, AMOUNT) AS Rno
FROM @MyTable
) A

I want My final resultset to look like this :

123 8214 Raaj KEVIN,GEORGE 200
124 8000 Ram SHANE 100
128 9000 VIJ BOND,STEVE 500

AS you can see in the above result set when ID,CODE,NAME and AMOUNT have same values, I would like to show the CONTACTPERSON concatenated (seperated by comma) added to the previous which has the same values.

Thanks,
Raaj.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-03 : 21:08:53

[code]
select ID, Code, Name,
ContactPerson = STUFF((SELECT ',' + x.ContactPerson
FROM @MyTable x
WHERE x.ID = t.ID
AND x.Code = t.Code
AND x.Name = t.Name
ORDER BY ',' + x.ContactPerson
FOR XML PATH('')), 1, 1, ''),
Amount = max(Amount)
from @MyTable t
group by ID, Code, Name
[/code]

KH
[spoiler]Time is always against us[/spoiler]

raaj
Posting Yak Master

129 Posts

Posted - 2009-12-06 : 17:22:01

Thanks Khtan,
That worked perfect.

Thanks,
Raaj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources