T-SqL help please

• nari.koud

  Ten Centuries

  Points: 1116

  More actions

  October 27, 2010 at 8:00 am

  #242251

  HI Experts Good Morning ....

  I need some help.....

  step 1.

  Create table orders1111

  (Err_Stmt Varchar(400),Error_At_line int,PIC_Number INT , ID_Number Varchar(14))

  step2.

  Insert Into Orders1111 values ('missing data',59,123,098989)

  Insert Into Orders1111 values ('missing data',59,113,12233444)

  Insert Into Orders1111 values ('missing data',59,123,0893849)

  Insert Into Orders1111 values ('Invalid data',79,123,09866)

  Insert Into Orders1111 values ('invalid data',79,123,451411)

  Insert Into Orders1111 values ('out of the Country',09,193,0985266)

  Insert Into Orders1111 values ('out of the dictionary',109,123,091245)

  Insert Into Orders1111 values ('out of the dictionary',109,123,0909889)

  step 3.

  select * from orders1111

  we get all the information

  But I want the output as as

  Err_Stmt Error_At_line count(*) ID_Number

  missing data 59 3 098989

  12233444

  0893849

  Invalid data 79 2 09866

  451411

  out of the Country 09 1 0985266

  out of the dictionary 109 2 091245

  0909889

  I know the Group by Function dont work here ..is there any script that would give me the desired output

  Please let me know

  Thanks

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 27, 2010 at 8:09 am

  #1242909

  ;WITH groupedOrders AS (

  SELECT Err_Stmt, Error_At_line, COUNT(*) AS [count(*)]

  FROM orders1111

  GROUP BY Err_Stmt, Error_At_line

  )

  SELECT *, ID_Number = STUFF((

  SELECT ',' + ID_Number

  FROM orders1111

  WHERE Err_Stmt = G.Err_Stmt

  AND Error_At_line = G.Error_At_line

  FOR XML PATH('')

  ),1,1,SPACE(0))

  FROM groupedorders AS G

  Hope this helps

  Gianluca

  -- Gianluca Sartori