sql syntax

I have below sql script. It works fine but I need to make it like summary in ID.
Attached excel has my original result from below query, and also the new result I would like to see.
Overall, I want to have a list of ID into a row. so I can create the report the way I want.
See any experts can help me. Thanks

SELECT     ID, Status, SectorVP, SectorVPApproval, SectorVPComments, DateSubmission, CURRENT_TIMESTAMP AS SystemCurrentTimeStamp, DATEDIFF(hh, DateSubmission,
                      CURRENT_TIMESTAMP) AS Hours_Difference
FROM         TravelApprovalForms
WHERE     (Status = 'Pending') AND (SectorVPApproval = 'Pending') AND (DateSubmission >= '09/01/2014') AND (DATEDIFF(hh, DateSubmission, CURRENT_TIMESTAMP) >= 24)
ORDER BY ID DESC

Original Result set:							
ID	STATUS	SECTORVP	SECTORVPAPPROVAL	SECTORVPCOMMENTS	DATESUBMISSION	SYSTEMCURRENTTIMESTAMP	HOURS_DIFFERENCE
548	Pending	TESTER	Pending		8/10/2014 9:34	9/10/2014 17:16	32
546	Pending	TESTER	Pending		7/10/2014 15:55	9/10/2014 17:16	50
545	Pending	TESTER	Pending	xxx	7/10/2014 15:54	9/10/2014 17:16	50
534	Pending	TESTER1	Pending	testing	7/10/2014 11:52	9/10/2014 17:16	54
580	Pending	TESTER1	Pending	testing	7/10/2014 11:52	9/10/2014 17:16	54
							
							
New Result set							
							
TESTER	548,546	
TESTER1	534, 580
					

Open in new window

LVL 1
ITsolutionWizardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Seems you are seeking a comma separated list of IDs per SECTORVP, but why is 545 missing in the result? commas.png
0
PortletPaulfreelancerCommented:
This result
| SECTORVP |       COLUMN_1 |
|----------|----------------|
|   TESTER |  545, 546, 548 |
|  TESTER1 |       534, 580 |

Open in new window


Produced by this query:
SELECT
      TAF.SECTORVP
    , MAX(CA.IDS)
FROM TravelApprovalForms AS TAF
    CROSS APPLY (
        SELECT
          STUFF((
                SELECT
                      ', ' + CAST(TAF2.ID AS varchar(20))
                FROM TravelApprovalForms AS TAF2
                WHERE TAF.SECTORVP = TAF2.SECTORVP
                ORDER BY TAF2.ID
                FOR XML PATH ('')
                )
             , 1, 1, '')
         ) AS CA (IDS)
GROUP BY
      SECTORVP
;

Open in new window

0
ITsolutionWizardAuthor Commented:
Great. thank but I try to add below
 where VPname <> null and totalListItem <> null

and the full script is below.

are not working :-(


SELECT ltrim(TAF.SECTORVP) as VPname, MAX(CA.IDS) as TotalListItem FROM TravelApprovalForms  

AS TAF
    CROSS APPLY (
        SELECT
          STUFF((
                SELECT
                      ', ' + CAST(TAF2.ID AS varchar(20))
                FROM TravelApprovalForms AS TAF2
                WHERE TAF.SECTORVP = TAF2.SECTORVP
                          ORDER BY TAF2.ID
                FOR XML PATH ('')
                )
             , 1, 1, '')

         ) AS CA (IDS)  
GROUP BY
      where VPname <> null and totalListItem <> null

      ltrim(SECTORVP)
        
        
;
0
PortletPaulfreelancerCommented:
NULL cannot equal anything and it cannot be unequal to anything

when filtering for NULLs you MUST use
IS NULL
IS NOT NULL


additionally the WHERE clause MUST happen before the GROUP BY clause


WHERE VPname IS NOT NULL and totalListItem IS NOT NULL
GROUP BY
      ltrim(SECTORVP)
       
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.