Ssrs - Group_Concat Equivalent Using an Expression

SSRS - Group_Concat Equivalent using an Expression?

try something like this (works on SQL Server 2005 and up):

set nocount on;
declare @t table (id int, name varchar(20), x char(1))
insert into @t (id, name, x)
select 1,'test1', 'a' union
select 1,'test1', 'b' union
select 1,'test1', 'c' union
select 2,'test2', 'a' union
select 2,'test2', 'c' union
select 3,'test3', 'b' union
select 3,'test3', 'c' 
SET NOCOUNT OFF

SELECT p1.id, p1.name,
          stuff(
                   (SELECT
                        ', ' + x
                        FROM @t p2
                        WHERE p2.id=p1.id
                        ORDER BY name, x
                        FOR XML PATH('') 
                   )
                   ,1,2, ''
               ) AS p3
      FROM @t p1
     GROUP BY 
        id, name

OUTPUT:

id          name                 p3
----------- -------------------- ---------
1           test1                a, b, c
2           test2                a, c
3           test3                b, c

(3 row(s) affected)

How to merge and center for certain row in SQL/SSRS report?

Method 1: No real changes to dataset.

Add a parent row group with a header to your current tablix. Set the grouping to an expression such as (assuming S/No is from a field called SerialNumber

=CINT(Fields.SerialNumber.Value)

So here we just convert S/No to an integer so 1.0 and 1.1 both return 1

Now you will have a header row for each group of rows.

You can merge the cells in the header row and set the expression to

=FIRST(Fields!Description.Value)

You may have to force the order of the data in your dataset to ensure that 1.0 is always before 1.1 etc.

Method 2: Add the group names to your dataset

Note: This is written for SQL Server not MySQL but should be easy enough to translate if required

If this does not work then you could add the group headers into a new column in your dataset query, then just group on that. The dataset query would look something like this...

(I've replicated your data to show it working)

DECLARE @myTable TABLE (SerialNumber decimal (5,1), Description varchar(50), UOM varchar(50), rate decimal (10,2))
INSERT INTO @myTable VALUES
(1.0, 'Warehouse Charges', NULL, NULL),
(1.1, 'Storage in pallet', 'perpallet per month or part thereof', 15.84),
(2.0, 'Handling', NULL, NULL),
(2.1, 'Unstuffing - Palletised', 'per pallet', 5.00),
(2.2, 'De-palletised', 'per palett', 5.00)

SELECT * FROM @myTable

    SELECT 
        b.Description as GroupName, a.* 
        FROM @myTable a 
          JOIN (SELECT SerialNumber, Description FROM @myTable WHERE CAST(SerialNumber AS INT) = SerialNumber) b -- headers only
            ON CAST(a.SerialNumber AS INT) = b.SerialNumber 
        WHERE a.SerialNumber != b.SerialNumber

This produces the following output

So now you can just group on the groupname field and then merge as described in the earlier method.

Returning many-to-one composite in SQL in one row

This should give you a single row per user in the following format

WITH cte (userId, roleList, roleNameTemp, level)
as 
(
    SELECT  userId
            , CAST( '' AS VARCHAR(max) )
            , CAST( '' AS VARCHAR(200) )
            , 0 level
    FROM    Role
    GROUP BY UserId 
    UNION ALL
    SELECT  r.userId
            , roleList + 
                CASE WHEN level = 0 THEN '' ELSE ', ' END + RoleName 
            , CAST( RoleName AS VARCHAR(200))
            , level + 1
    FROM CTE c
    INNER JOIN Role r ON c.userId = r.userid
    WHERE   r.RoleName  > c.roleNameTemp 
)

SELECT      UserId
            ,FirstName
            ,LastName
            ,roleList 
FROM 
(
    SELECT      u.UserId
                ,u.FirstName
                ,u.LastName
                ,c.roleList 
                ,ROW_NUMBER() OVER (Partition by c.userid order by level desc) rowNumber
    FROM        cte c
    INNER JOIN  [User] u on c.userId = u.UserId
)   r
WHERE rownumber = 1

How to get PageName by Group for a Tablix with multiple groups

If not already done, use page breaks accordingly and set the expression of a group-level TextBox (cell in the Tablix) to contain the value that you want to be displayed in the page header or footer.

Then, in a textbox in the header or footer, reference that textbox in an expression like this:

=ReportItems!NameOfTextBox.Value

This should do the trick.

sql 2005 grouping data that is dynamic

This should work:

SELECT Investor, 
STUFF((
    SELECT ',' + convert(nvarchar(50), Contact) 
    FROM Investors I2
    WHERE I2.Investor = I1.Investor
    FOR XML PATH('')
), 1, 1, '') Contacts
FROM Investors I1
GROUP BY Investor

And result in:

IBM       James,Dean,Sean
Microsoft   Bill,Steve

SUM string with comma (SQL Server Reporting Services)

SAMPLE TABLE

CREATE TABLE #TEMP(COLUMN1 VARCHAR(100),COLUMN2 VARCHAR(100))

INSERT INTO #TEMP
SELECT 'jack',1  
UNION ALL
SELECT 'john',1 
UNION ALL
SELECT 'bill',1
UNION ALL
SELECT 'joe',1
UNION ALL
SELECT 'sindy',1

QUERY

If you need the value with comma separate values and count, you can avoid query before UNION ALL and execute the query only after UNION ALL.

SELECT NULL,COLUMN1,COLUMN2
FROM #TEMP

UNION ALL

SELECT DISTINCT 'TOTAL',
-- Here we convert to comma separated values
SUBSTRING(
            (SELECT  ', ' + COLUMN1
            FROM #TEMP T2 
            --WHERE C2.Id=Id AND C2.COLUM=COLUM            
            FOR XML PATH('')),2,200000) COLUMN1,
COUNT(COLUMN1) COLUMN2
FROM #TEMP T1

---

Related Topics