-6

Help me with a SQL query. I would get all values, like '01,02,03,20,92,93,94" values from first query, but instead of function MAX in two query. Result:in opposite b_BookNum I would to see one string:'01,02,03,20,92,93,94'

enter image description here

Serg Bash
  • 91
  • 1
  • 12
  • 3
    Please try to work a bit harder at explaining what you want. Putting sample data and desired results into the question is a big help -- and much easier to read than an image. – Gordon Linoff Jul 16 '15 at 12:22
  • Correct. Put sample data and expected data instead of showing some result. – Ravi Jul 16 '15 at 12:25
  • 2
    Still not so clear to me. If you want them all then why are you even grouping? – Crono Jul 16 '15 at 12:27

1 Answers1

0

Simply take out the MAX() function and add this column in your group by clause , happy days......

SELECT  [b_BookNum]
       ,[b_CHMAT]
       ,COUNT(*) iCount
FROM MPA.dbo.SCHM_Books
GROUP BY [b_BookNum] ,[b_CHMAT]
ORDER BY [b_BookNum]

EDIT

I am surprised that you have all the information you need to solve the problem and yet you are so incompetent to solve it. Anyway here is the full solution:

;WITH CTE AS (
SELECT  [b_BookNum]
       ,COUNT(*) iCount
FROM MPA.dbo.SCHM_Books
GROUP BY [b_BookNum] 
)
SELECT [b_BookNum]
      ,STUFF((SELECT ',' + CAST([b_CHMAT] AS VARCHAR(10))
      FROM dbo.SCHM_Books 
      WHERE [b_BookNum] = C.[b_BookNum]
      FOR XML PATH(''),TYPE)
      .value('.','NVARCHAR(MAX)'),1,1,'') AS [b_CHMAT]
      ,iCount
FROM CTE C
M.Ali
  • 67,945
  • 13
  • 101
  • 127