[Solved] Get all values from SQL Server [closed]

by


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

7

solved Get all values from SQL Server [closed]