November 25, 2011 at 12:28 am
How does one group data horizontally? For instance, I have the following three columns.
What I want is to split the columns into rows of four and display it next to each other with the headers as in the first group
G P A
01:04 15 34 5.1
01:04 150 9 13.5
01:04 25 14 3.5
01:04 250 7 17.5
01:04 35 13 4.55
01:04 45 13 5.85
01:04 55 23 12.65
01:04 550 4 22
01:10 125 2 2.5
01:10 75 2 1.5
01:25 125 2 2.5
01:25 75 2 1.5
A HWH 75 9 6.75
B 55 8 4.4
C attrak 55 5 2.75
D vlees 45 18 8.1
E spes at 25 27 6.75
F DJD 15 87 13.05
HWH 15 115 17.25
S2 55 1 0.55
S3 55 19 10.45
November 25, 2011 at 2:03 am
November 28, 2011 at 2:04 am
Hi guys and girls
I presently have a table for which the data is displayed as the following:
G P A
1:04 |15 34 |5.1
01:04 |150 9 |13.5
01:04 |25 14 |3.5
01:04 |250 7 |17.5
01:04 |35 13 |4.55
01:04 |45 13 |5.85
01:04 |55 23 |12.65
01:04 |550 |4 22
01:10 |125 |2 2.5
01:10 |125 |2 2.5
01:25 |75 |2 1.5
A |HWH 75 | 9 6.75
B |55 |8 4.4
C |attrak 55 |5 2.75
Is there some clever person among you that can display the data like the following: Next to each other grouped as rows of fours(4).
G P A G P A G P A G P A
01:04 |15 34 |5.1 01:04 |35 13 |4.55 01:10 |125 |2 2.5 01:25 |75 |2 1.5
01:04 |150 9 |13.5 01:04 |45 13 |5.85 01:10 |125 |2 2.5 A |HWH 75 | 9 6.75
01:04 |25 14 |3.5 01:04 |55 23 |12.65 01:10 |75 |2 1.5 B |55 |8 4.4
01:04 |250 7 |17.5 01:04 |550 |4 22 01:25 |125 |2 2.5 C |attrak 55 |5 2.75
...
Kind regards and thank you for your assistance.
Fred
P.S. This is the code I use to generate the above code in ssrs:
SELECT orbpd.BettingPoolNumber
,orbpd.[Name] + ' ' + CONVERT(NVARCHAR(MAX),(orbpd.BettingAmountPerPigeonOrPigeonGroup * 100)) AS G
,orbpd.[RaceId]
,orbpd.[OrganizationId]
,orbpd.[OrganizationHierarchyId]
,[TotalPoolAmount] AS A
,(SELECT SUM(NumberOfPigeonsPooled)
FROM MemberRaceBettingPoolData
WHERE RaceId = orbpd.[RaceId]
AND LevelNumber = o.[OrganizationBettingPoolLevelNo]
AND PoolNumber = orbpd.[BettingPoolNumber]) AS P
FROM [OrganizationRaceBettingPoolData] orbpd
INNER JOIN [Organization] o
ON orbpd.[OrganizationId] = o.[OrganizationId]
AND orbpd.[OrganizationHierarchyId] = o.[OrganizationHierarchyId]
WHERE orbpd.RaceId=@RaceId
AND orbpd.OrganizationId=@OrganizationID
November 28, 2011 at 2:15 am
I suppose your question is not important enough to post it properly.
Did you read the article I suggested?
-- Gianluca Sartori
November 28, 2011 at 2:24 am
It's rather a simple question: I have a table with 25 rows being displayed vertically and I want to display it next to each other in rows of fours.
Kind regards
November 28, 2011 at 2:43 am
Gianluca Sartori (11/25/2011)
Columns to rows looks like a good fit for UNPIVOT.However, I don't understand the expected output.
Can you please post your question according to the specs in this article[/url]?
As Gianluca said in his earlier post within the article, we need consumable data and DDL in a format which can be run in order to help. There was also a lead in that the UNPIVOT command is what you are looking for.
You have to remember that the people on here are unpaid volentieers who have full time jobs and cannot commit the time it takes to generate the information they need to help you, so please help us to help you and provide consumable data so we can get you an answer quicker.
November 28, 2011 at 8:43 am
frdrckmitchell7 (11/28/2011)
It's rather a simple question: I have a table with 25 rows being displayed vertically and I want to display it next to each other in rows of fours.Kind regards
If this is simply for display (i.e. a report or similar), you should do this on the front end. It is about a million times easier to do this in the front end. It can be done in sql but it is NOT easy.
Yes, the question is pretty simple but the solution is not. This means that Gianluca (although I suspect he is unlikely to help you at this point), or somebody else will have to generate sample data to help you fix your problem. If your problem is as simple as you state then it should only take a few minutes to put together the ddl and sample data (create table and insert statements) and desired output based on that sample data.
If you have to do this is in sql then you will probably need to pivot/unpivot and use row_number. At that point it may be easier to use a temp table and populate it from your base table.
_______________________________________________________________
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 28, 2011 at 9:18 am
Sean Lange (11/28/2011)
This means that Gianluca (although I suspect he is unlikely to help you at this point), or somebody else will have to generate sample data to help you fix your problem.
I don't mind creating sample data for those that don't know how to do it, I have done it many times in the past.
In this particular case, I don't understand which value belongs to which column.
-- Gianluca Sartori
November 28, 2011 at 10:57 am
....looking at whatt has been provided by the OP, I have a suspicion that the requirement is to maybe concatenate rows based on
RN 1 + RN 5 + RN 9....
RN 2 + RN 6 + RN 10....
RN 3 + RN 7 ...and so on.
probably totally wrong and without further details from OP ...just a guess.
As for the business reason........who knows 😉
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 28, 2011 at 11:22 am
OK i've played with something similar..i wanted to orgianize data for a web page into 5 columns, instead of one increadabily tall column of data for a web page.
here's an example:
--only showing 24 rows inc ase you have 1000 + tables:
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
November 28, 2011 at 1:21 pm
frdrckmitchell7 (11/28/2011)
It's rather a simple question: I have a table with 25 rows being displayed vertically and I want to display it next to each other in rows of fours.Kind regards
Dear Fred.......seems you are very new to this site.
My thoughts are : that if you are asked to provide more detail...particularly in relation to set up scripts / example data / and what you have tried so far....then you are far more likely to receive a positive and tested answer....if you are not sure how to provide such data then please ask us.
You may well believe that you have asked "a rather simple question".....however, as others have already posted...its not that "simple" in TSQL and maybe better presented in your report / app.
but sometimes needs must....and therefore below is a "possible" solution, based on what I believe you have asked.
please remember that so far you have not provided any suitable scripts that will allow us to easily reconstruct your problem and as such the following may not in anyway provide what your are looking for 🙂
--based on Lowell's earlier post
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GPA]') AND type in (N'U'))
DROP TABLE [dbo].[GPA]
GO
CREATE TABLE [dbo].[GPA](
[G] [varchar](50) NULL,
[P] [varchar](50) NULL,
[A] [varchar](50) NULL,
[ROWID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.GPA ON
INSERT INTO [dbo].[GPA]([G], [P], [A], [ROWID])
SELECT N'01:04', N'15 34 ', N'5.1', 1 UNION ALL
SELECT N'01:04', N'150 9 ', N'13.5', 2 UNION ALL
SELECT N'01:04', N'25 14 ', N'3.5', 3 UNION ALL
SELECT N'01:04', N'250 7 ', N'17.5', 4 UNION ALL
SELECT N'01:04', N'35 13 ', N'4.55', 5 UNION ALL
SELECT N'01:04', N'45 13 ', N'5.85', 6 UNION ALL
SELECT N'01:04', N'55 23 ', N'12.65', 7 UNION ALL
SELECT N'01:04', N'550', N'4 22', 8 UNION ALL
SELECT N'01:10', N'125', N'2 2.5', 9 UNION ALL
SELECT N'01:10', N'125', N'2 2.5', 10 UNION ALL
SELECT N'01:25', N'75', N'2 1.5', 11 UNION ALL
SELECT N'A ', N'HWH 75 ', N' 9 6.75', 12 UNION ALL
SELECT N'B ', N'55', N'8 4.4', 13 UNION ALL
SELECT N'C ', N'attrak 55 ', N'5 2.75', 14
SET IDENTITY_INSERT dbo.GPA OFF
;WITH CTE as
(
SELECT TOP 100 PERCENT
G+' '+ P + ' ' +A as DETS,
( Row_number() OVER (ORDER BY ROWID) - 1 )%4 + 1 AS RNGRP
FROM dbo.GPA
ORDER BY ( Row_number() OVER (ORDER BY ROWID) - 1 )%4 + 1
)
SELECT RNGRP,
Stuff((SELECT ',' + DETS
FROM CTE p2
WHERE p1.RNGRP = p2.RNGRP
ORDER BY p2.RNGRP
FOR XML PATH('')), 1, 1, ' ')
FROM CTE p1
GROUP BY RNGRP
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 28, 2011 at 11:27 pm
Hi guys
Thank you all for your kind responses. I have attached data with an Excel file for anyone to upload. The file consists of the data that I currently have and then what I want it to look like in ssrs.
Kind regards and thanks for all you help on this one.
Fred
November 29, 2011 at 10:45 am
Fred, your explanation is still unclear. Can you post ddl (create table scripts), sample data (insert statements) and the desired output based on your sample data? The question is very clear in your head but none of us are understanding what the solution is because we can't see your tables or data and we are not familiar with your project. My guess is we can knock this out pretty quickly if you provide enough details for somebody to help you.
_______________________________________________________________
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 12:37 pm
Is this what your table and data look like?
create table #GAP
(
G varchar(25),
A numeric(9,2) null,
P int null
)
Insert #GAP
select 'HWH 15',17.25, 115
union all select 'SP1 15',NULL, NULL
union all select 'SP2 25',NULL, NULL
union all select 'SP3 55',NULL, NULL
union all select 'S2 55',0.55, 1
union all select 'S3 55',10.45, 19
union all select 'S4 55',NULL, NULL
union all select 'A HWH 75',6.75, 9
union all select 'B 55',4.4, 8
union all select 'C attrak 55',2.75, 5
union all select 'D vlees 45',8.1, 18
union all select 'E spes at 25',6.75, 27
union all select 'F DJD 15',13.05, 87
select * from #GAP
Assuming that is correct, what should the output be?
_______________________________________________________________
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 12:44 pm
i think he wants to pivot three columns into twelve total columns.(four sets of three), but i'm just guessing at this point.
Lowell
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply