February 11, 2009 at 10:24 am
Hello,
I have the query below working thanks to members of this board but still need help with one more thing. Because the contactanniversarydate table has multiple entries of the same contacts with the only difference between the multiple records being different anniversary dates, the query below will return each as a sperate record. For example, John Smith will appear twice in the resultset with every thing being duplicate in both returned records except that each returned record has a different anniversary date, thus the reason it returned two records for John Smith. How can I combine both anniversary dates into a single field and have that returned as a single record from this query? I know how to use the UNION operator to combine two different fields in the same record such as firstname and lastname but this requires combining data from different fields in the same column, from two or more different related records, into a single field.
ie-
Name Anniversary Date Group
John Smith 10/22/2009 Beta
10/21/2009
Mary Jones 8/2/2009 Alpha
As opposed to:
Name Anniversary Date Group
John Smith 10/22/2009 Beta
John Smith 10/21/2009 Beta
Mary Jones 8/2/2009 Alpha
SELECT a.contactid, a.firstname, a.middlename, a.lastname, a.contacttype, b.anniversarydate, c.groupname, a.creatinguserid, d.fullname, e.deptname
FROM contact a
INNER JOIN contactanniversarydate b
ON a.contactid=b.contactid
INNER JOIN group c
ON a.groupid=c.groupid
INNER JOIN users d
ON a.creatinguserid = d.userid
INNER JOIN deptlist e
ON d.deptcode = e.deptcode
GROUP BY a.contactid, a.firstname, a.middlename, a.lastname, a.deptid, a.contacttype, b.anniversarydate, c.deptname, a.userid, d.fullname, e.groupname
ORDER BY a.lastname
Thanks
February 12, 2009 at 7:42 am
1st thoughts (Note there are probably more elegant ways to do this if I thought about it longer)
1. Get a list of all of the contactids and anniversary dates
2. Generate a table which uniqely lists the contactids and and also a working field to store
3. Loop through the list of contactids and anniversary dates from step 1 and append this onto the anniversary dates field from step 2. So use charindex to verify if the date is in the anniversary date field
4. Once you finish the loop, you can add this to your join and replace the grab of the anniversary date.
There's various ways to do it.. this is just one of them.
February 16, 2009 at 2:09 am
before this select * from RAJ_TEST WILL LOOK LIKE BEFORE I IMPLEMENT BELOW QUERY
No Name Salary
11 PRAVIN 10000
12 pravin 9000
1 Pravin 15000
2 Parag 20000
3 Herry 10000
4 Jeff 2000
5 Hardik 4000
6 Ramesh 6000
7 Mitesh 8000
8 Siddhi 7000
9 Karan 9000
10 Riddhi 12000
SELECT NAME ,[salary] FROM
(
SELECT b.NAME AS NAME ,salary = REPLACE ((SELECT salary AS [data()] FROM RAJ_TEST AS a WHERE a.[Name] = b.NAME
ORDER BY NAME FOR XML PATH ('')),' ',',')
FROM RAJ_TEST AS b INNER JOIN RAJ_TEST AS c ON c.NO= b.NO ) d
GROUP BY NAME ,[salary]
result for this is
NAME salary
Hardik 4000
Herry 10000
Jeff 2000
Karan 9000
Mitesh 8000
Parag 20000
PRAVIN 10000,9000,15000
Ramesh 6000
Riddhi 12000
Siddhi 7000
man i work so much to find out this............... may be it will work for you .......or you can send me some sample data with temp table to do that
Raj Acharya
February 17, 2009 at 2:33 pm
You can even use COALESCE.
Check out the below link
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
Thanks
Vijaya Kadiyala
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply