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
NoNameSalary
11PRAVIN10000
12pravin9000
1Pravin15000
2Parag20000
3Herry10000
4Jeff2000
5Hardik4000
6Ramesh6000
7Mitesh8000
8Siddhi7000
9Karan9000
10Riddhi12000
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
NAMEsalary
Hardik4000
Herry10000
Jeff2000
Karan9000
Mitesh8000
Parag20000
PRAVIN10000,9000,15000
Ramesh6000
Riddhi12000
Siddhi7000
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