August 31, 2017 at 5:25 am
Hi,
I'm creating a report that has to display values horizontally, the values need to be distinct and then comma delimited (but not the last one) and just 7 columns per row
I can't do a select distinct in my query, otherwise the ceiling-function (ceiling(rownumber(nothing) mod 6)) in ColumnGroup and (=ceiling(rownumber(nothing) / 6)) in RowGroup would do the trick in group but as I need grouping the values I can't do that.
I write this report in MDX and because of other pages in the report I can't use distinct values in the query.
So I need the report look like this:
Sellreport for products:
AdjustableRace, Bearing Ball, BB Ball Bearing, Headset Ball Bearings, Blade, LL Crankarm, MLCrankarm,
HL Crankarm, Chainring Bolts, Chainring Nut, Chainring, Crown Race, ChainStays, Decal 1,
Decal 2, Down Tube, Mountain End Caps, Road End Caps, Touring EndCaps, Fork End, Freewheel
Is there someone here that has a good solution?
Regards,
M
August 31, 2017 at 6:05 am
so do you want to arrange your data as columns, or one column that is comma delimted?
yopu did not provide an example query or expected output, but here's two examples:
this produces two columns, the main column, and the other column containing acomma delimited list:SELECT DISTINCT
t.name,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + name
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
and her eis an example of spreading out the data into multiple columns, isntead of one long, run on column; is that what you are after?
--the correct example:
;WITH
baseCTE AS
(
SELECT TOP(24*5)
(ROW_NUMBER() OVER (ORDER BY Name)-1)/5+1 AS RW,
(ROW_NUMBER() OVER (ORDER BY Name)-1)%5+1 AS CL,
Name
FROM sys.tables
)
SELECT MAX(CASE WHEN CL = 1 THEN Name ELSE '' END) AS Col1TableName,
MAX(CASE WHEN CL = 2 THEN Name ELSE '' END) AS Col2TableName,
MAX(CASE WHEN CL = 3 THEN Name ELSE '' END) AS Col3TableName,
MAX(CASE WHEN CL = 4 THEN Name ELSE '' END) AS Col4TableName,
MAX(CASE WHEN CL = 5 THEN Name ELSE '' END) AS Col5TableName
FROM baseCTE
GROUP BY RW
Lowell
August 31, 2017 at 7:02 am
Lowell - Thursday, August 31, 2017 6:05 AMso do you want to arrange your data as columns, or one column that is comma delimted?yopu did not provide an example query or expected output, but here's two examples:
this produces two columns, the main column, and the other column containing acomma delimited list:SELECT DISTINCT
t.name,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + name
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_idand her eis an example of spreading out the data into multiple columns, isntead of one long, run on column; is that what you are after?
--the correct example:
;WITH
baseCTE AS
(
SELECT TOP(24*5)
(ROW_NUMBER() OVER (ORDER BY Name)-1)/5+1 AS RW,
(ROW_NUMBER() OVER (ORDER BY Name)-1)%5+1 AS CL,
Name
FROM sys.tables
)
SELECT MAX(CASE WHEN CL = 1 THEN Name ELSE '' END) AS Col1TableName,
MAX(CASE WHEN CL = 2 THEN Name ELSE '' END) AS Col2TableName,
MAX(CASE WHEN CL = 3 THEN Name ELSE '' END) AS Col3TableName,
MAX(CASE WHEN CL = 4 THEN Name ELSE '' END) AS Col4TableName,
MAX(CASE WHEN CL = 5 THEN Name ELSE '' END) AS Col5TableName
FROM baseCTE
GROUP BY RW
Thank's for the reply,
This could have work, but unfortunately I can't do this in T-SQL because I have to get the values from the cube, so therefore I need to do this in MDX, but I can't change my ordinary MDX because in some parts in the report I need it as not distinct.
But thank's a lot anyway 🙂
August 31, 2017 at 7:04 pm
Instead of changing the MDX why not create a new dataset for this particular feature ?
September 1, 2017 at 3:08 am
matak - Thursday, August 31, 2017 7:04 PMInstead of changing the MDX why not create a new dataset for this particular feature ?
Thank's for the answer,.
Unfortunately so can't I create a new dataset because the records is depending on the dataset 1.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply