November 29, 2011 at 12:50 pm
Lowell, I am thinking it might be a report so some string concatenation and your previous example might be sufficient. Just cast each column to a fixed length varchar to keep alignment. It's a total kludge but then again so is doing this in the db in the first place.
Of course that might not work if the actual display is using a non fixed width font. I guess at that point you could use for xml and generate an html table or something similar.
Fred - you still with us?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 11:17 pm
Hi Sean
Your sql example is right that you have coded above. That is what I have now, one table displaying its contents vertically (as per normal display). In ssrs, I want to split the your 'example' into groups of four, each with its own header (G A P), but next to each other, dynamically. This must be done in reporting services with a matrix or something.
You would thus have:
GAP |G AP
HWH 1517.25115 |S2 55 0.551
SP1 15NULLNULL |S3 55 10.4519
SP2 25NULLNULL |S4 55 NULLNULL
SP3 55NULLNULL |A HWH 75 6.759
...
with the other columns following as above.
We want it that way so that we can save space on a pdf booklet that will have other information in it.
Kind regards
Fred
November 30, 2011 at 7:12 am
Could you build it with three datasets? As long as you have something that will keep the order consistent that might be easier than trying to do that all in a single query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2011 at 2:48 am
Thanks for everyone's input. I have sorted this one out.
Kind regards
December 1, 2011 at 7:33 am
frdrckmitchell7 (12/1/2011)
Thanks for everyone's input. I have sorted this one out.Kind regards
Glad you figured it out. Can you post your solution so others that stumble in here have an idea of how you fixed it. That may give them the ability to fix their situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2011 at 11:37 pm
Hi people, this is how I resolved this:
1) Here's my query that I entered into my dataset:
IF OBJECT_ID('tempdb..#testdata') IS NOT NULL
BEGIN
DROP TABLE #testdata
END
SELECT orbpd.BettingPoolNumber
,o.Acronym + ' - ' + o.Description as Organisatie
,orbpd.[Name] + ' ' + CONVERT(NVARCHAR(MAX),(orbpd.BettingAmountPerPigeonOrPigeonGroup * 100)) AS G
,orbpd.[RaceId]
,orbpd.[OrganizationId]
,orbpd.[OrganizationHierarchyId]
,o.Acronym + ' - ' + o.Description + ' (' + CONVERT(NVARCHAR(max),o.OrganizationBettingPoolLevelNo) + ')' as OrganizationLevel
,r.RaceCode + ' - ' + r.Description as Race
,[TotalPoolAmount] AS A
,(SELECT SUM(NumberOfPigeonsPooled) FROM MemberRaceBettingPoolData
WHERE RaceId = orbpd.[RaceId]
AND LevelNumber = o.[OrganizationBettingPoolLevelNo]
AND PoolNumber = orbpd.[BettingPoolNumber]) AS P
INTO #testdata
FROM [OrganizationRaceBettingPoolData] orbpd
INNER JOIN [Organization] o
ON orbpd.[OrganizationId] = o.[OrganizationId]
AND orbpd.[OrganizationHierarchyId] = o.[OrganizationHierarchyId]
LEFT JOIN Race r
ON orbpd.RaceId = r.RaceId
WHERE OrganizationTypeId ='VERENIGING'
AND orbpd.RaceId=@RaceId
--AND o.ResultTypeId = 'ALLPIGEONS'
ORDER BY Organisatie;
SELECT Organisatie,
MAX( CASE WHEN (Rn-1)/4 = 0 THEN G ELSE '' END) AS G,
MAX( CASE WHEN (Rn-1)/4 = 0 THEN P ELSE '' END) AS P,
MAX( CASE WHEN (Rn-1)/4 = 1 THEN A ELSE '' END) AS A,
MAX( CASE WHEN (Rn-1)/4 = 1 THEN G ELSE '' END) AS G1,
MAX( CASE WHEN (Rn-1)/4 = 2 THEN P ELSE '' END) AS P1,
MAX( CASE WHEN (Rn-1)/4 = 2 THEN A ELSE '' END) AS A1,
MAX( CASE WHEN (Rn-1)/4 = 3 THEN G ELSE '' END) AS G2,
MAX( CASE WHEN (Rn-1)/4 = 3 THEN P ELSE '' END) AS P2,
MAX( CASE WHEN (Rn-1)/4 = 3 THEN A ELSE '' END) AS A2
FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY G) AS Rn
FROM #testdata
) t
GROUP BY Organisatie /*, (Rn-1)%4*/
2) I create a tablix and place the G,P,A,G1... next to each other.
3) then it's just a matter of adding your header expressions (the G,P,A,G1...) to something like this:
=left("G1",1)
and that will build your columns next to each other.
Thanks for all your helps leading me to this conclusion.
Kind regards
Fred
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply