Source
r/SQL•
ignathius

How to list all columns in report?

Hello. I'm extremly new to SQL and SSRS, and am having an issue getting a report to display properly and was hoping someone here could point me in the right direction.

What i'm trying to do, is display all of the IDs associated with a Parameter in the same cell. I'm not sure if this is something that needs to be done in the query, or just in the report. What i'm currently getting is:

Parameter ID

A 1

A 2

B 3

B 4

what i need is:

Parameter ID

A 1,2

B 3,4

I'm thinking i may need a function in the report to get this to work right, but not entirely certain. If anyone has any suggestions i'd greatly appreciate it :)

Posted May 9, 2011, 3:27 PM UTC

25
[deleted]•

Thats a crosstab report, you can achieve that by using the t-sql pivot statement

Select your results into a temporary table Pivot a select * from that table, this is the same as the transpose function in excel, i.e. row values become column header

SELECT * FROM #temptable PIVOT ( MAX(ID) FOR [Parameter] IN ([1],[2],[3],[4]) ) AS p

You should get a row a for Each Parameter

PIVOT ( MAX(ID)

--Return the maximum value in the id column for each

FOR [Parameter] IN ([1],[2],[3],[4])

1,2,3,4 now become column headers the table looks like

parameter,1,2,3,4

the max(id) is going to be the row value where the value of max(id)matches the same value in the column header

Paameter,1,2,3,4

a,1,2,null,null b,null,null,3,4

) AS p

for an unkown set of IDs you can generate a dynamic list of column headers as outlined here http://www.tsqltutorials.com/pivot.php

declare a3

2
xstockix•

You might check this out.

1
StopThinking•

Here's a way to use a function to do it in the query:

CREATE FUNCTION sfunc_GetIDs (@Parameter varchar(8))
RETURNS varchar(255)
AS
BEGIN

	DECLARE @IDs varchar(255)

	SET @IDs = ''

	SELECT @IDs = @IDs + CAST(id AS varchar) + ', '
	FROM Params
	WHERE Parameter = @Parameter

	RETURN LEFT(@IDs,LEN(@IDs)-1)
END
GO

SELECT DISTINCT
	Parameter
	,dbo.sfunc_GetIDs(Parameter)
FROM Params
1
elus•

If you're using SSRS why don't you take a look at the Matrix/Tablix control.

1
ignathiusOP•

thanks guys, i'll look into these and see if i can't get this report to format properly :)

1