May 17, 2017 at 4:56 am
DECLARE @Temp Table (ID int, Make varchar(2), Model varchar(2), ModelYear int, FuelType varchar(15), cylinder varchar(3) )
DECLARE @KmRange bigint = 30000
insert into @Temp
select 1, 'FO', '2E', 2015, 'Diesel', '4x4'
union all
select 2, 'FO', '2G', 2015, 'Diesel', '4x4'
union all
select 3, 'FO', '2H', 2015, 'Diesel', '4x4'
union all
select 4, 'IN', '2G', 2015, 'Diesel', '4x4'
select ID, Make, Model,ModelYear, FuelType,Cylinder, CONCAT(LowerRange,'-', UpperRange ) KmRange from @Temp
cross join
(
SELECT * FROM (
SELECT (@KmRange * (ROW_NUMBER() OVER(ORDER BY ID)-1)) AS LowerRange, @KmRange * ROW_NUMBER() OVER(ORDER BY ID) AS UpperRange, ROW_NUMBER() OVER(ORDER BY ID) AS segval
FROM @Temp fm
) A WHERE segval = 1 AND segval <= (FLOOR(300000/@KmRange))
union all
SELECT * FROM (
SELECT ((@KmRange * (ROW_NUMBER() OVER(ORDER BY ID)-1))) + 1 AS LowerRange, @KmRange * ROW_NUMBER() OVER(ORDER BY ID) AS UpperRange, ROW_NUMBER() OVER(ORDER BY ID) AS segval
FROM @Temp fm
) B WHERE segval <> 1 AND segval <= (FLOOR(300000/@KmRange))
) KmRange
order by 1
/* Expected Output */
select 1, 'FO', '2E', 2015, 'Diesel', '4x4', '' as "0-30000", '' as "30001-60000", '' as "60000-90000", '' as "90000-120000"
union all
select 2, 'FO', '2G', 2015, 'Diesel', '4x4', '','','',''
union all
select 3, 'FO', '2H', 2015, 'Diesel', '4x4', '','','',''
union all
select 4, 'IN', '2G', 2015, 'Diesel', '4x4', '','','',''
Friends,
I am trying to build a dynamic column (Ex: 0-20000,20001-40000,40001-60000 and so on) which depends upon user parameter @KmRange. Parameter value can be anything (ex: 30000 or 20000 or 40000).
Using CROSS JOIN , I am able to get that in rows. How can I convert into columns.
Thanks,
Charmer
May 17, 2017 at 6:25 am
I've run your query, and I just don't see what your objective is. You say you want to pivot a row, but how, exactly? What is the relationship between a given record in your first query result set, and the desire to capture information into the range columns. Doing any kind of dynamic column naming is challenging at best, and having done so, your ability to use a Reporting tool of any modern kind is going to likely be impossible without some significant changes in perspective. You've not described your data in any useful way, so I have no idea what you're really looking to accomplish. Please explain in considerably more detail what you mean by "pivot a row", as it relates to your data. I know HOW to pivot a row, but it's far more important to know 1) what the row represents, and 2) what the pivoted row is supposed to represent, as sometimes, pivot is a word that can apply rather differently to different situations, and in many cases, a true SQL pivot isn't necessary.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 17, 2017 at 6:29 am
Forgot to mention one other thing. Why do you have a WHERE clause that checks that the value of the "segval" field is both equal to 1 AND is less than that FLOOR function? If it's equal to 1, and @KmRange is greater than 300000, then no records will pass through that query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 17, 2017 at 6:47 am
Sorry. Post deleted. Misread a couple of things.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2017 at 7:47 am
sgmunson - Wednesday, May 17, 2017 6:25 AMI've run your query, and I just don't see what your objective is. You say you want to pivot a row, but how, exactly? What is the relationship between a given record in your first query result set, and the desire to capture information into the range columns. Doing any kind of dynamic column naming is challenging at best, and having done so, your ability to use a Reporting tool of any modern kind is going to likely be impossible without some significant changes in perspective. You've not described your data in any useful way, so I have no idea what you're really looking to accomplish. Please explain in considerably more detail what you mean by "pivot a row", as it relates to your data. I know HOW to pivot a row, but it's far more important to know 1) what the row represents, and 2) what the pivoted row is supposed to represent, as sometimes, pivot is a word that can apply rather differently to different situations, and in many cases, a true SQL pivot isn't necessary.
Sorry that I did not give enough information. Actually I am trying to build a SSRS report. This is regarding maintenance report of a vehicle.
Last column of the report is KM Range which must be dynamic. and it is based on an user input. Based On the input KM range band the reports populate n number of columns.
Example: If 20000 is given as KM range band input then
0-20000,20001-40000 etc up to 280000-300000 different KM range band will be created. The KM Range band should not be more than 300000. So that's why I have used the calculation (300000/@KM Range) in my WHERE clause.
so if @KM range = 40000 then columns will be created 0-40000, 40001-80000, etc... up to 240000-280000.
The values under this n number of dynamic columns are being done in the report design. Values will be like Average cost spent on a vehicle for each Kilo meter range band. And finally Total value.
Thanks,
Charmer
May 17, 2017 at 8:59 am
Okay, so what's your plan in terms of SSRS (Reporting Services) as to how to tie your data source to your report control? You can NOT run the report and have dynamic column names coming from the query. That just can NOT work. You might be able to use a cross-tab if you can figure out how to control the number of ranges entirely within SSRS and NOT within your data source. Your data source MUST be consistent with columns, or you can forget about using SSRS. The only way is to have some kind of dynamic grouping on range within your cross-tab. This just isn't going to be supportable on the query side of things. You'll have to have data rows that have a Km value associated with them and then group those rows into the dynamic ranges.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 17, 2017 at 9:03 am
You should pivot your values in SSRS. That will be more efficient.
Here's how you do it: https://docs.microsoft.com/en-us/sql/reporting-services/report-design/create-a-matrix-report-builder-and-ssrs
May 17, 2017 at 9:03 am
And while I'm thinking about it, you'll need to realize that you won't have a terribly good looking report if someone chooses 1,000 km increments, or worse yet, 500 km or even 100 km. Think about what happens when you get 300,000 divided by 1,000 or 500, or 100. Having 300, 600, or 3,000 columns in your result just isn't practical.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 17, 2017 at 9:12 am
sgmunson - Wednesday, May 17, 2017 8:59 AMOkay, so what's your plan in terms of SSRS (Reporting Services) as to how to tie your data source to your report control? You can NOT run the report and have dynamic column names coming from the query. That just can NOT work. You might be able to use a cross-tab if you can figure out how to control the number of ranges entirely within SSRS and NOT within your data source. Your data source MUST be consistent with columns, or you can forget about using SSRS. The only way is to have some kind of dynamic grouping on range within your cross-tab. This just isn't going to be supportable on the query side of things. You'll have to have data rows that have a Km value associated with them and then group those rows into the dynamic ranges.
I just realized that what I said above could be interpreted incorrectly. You could use the supplied increment value as a parameter to your data source, and identify the range that a given record falls in within the data source query. This would then allow you the grouping capability within a cross-tab report control. The way I worded things above, one could have easily concluded that such was neither appropriate nor practical, when it's pretty much the only viable way to go. Mea culpa.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 17, 2017 at 9:13 am
Apparently, I left the code window open from when Jeff deleted the thread and I couldn't post anymore. It changes how you create the the ranges, so I'll post it as general knowledge but I still believe that the pivot should be done in SSRS.
CREATE TABLE #Temp (ID int, Make varchar(2), Model varchar(2), ModelYear int, FuelType varchar(15), cylinder varchar(3) );
DECLARE @KmRange bigint = 5000;
insert into #Temp
select 1, 'FO', '2E', 2015, 'Diesel', '4x4'
union all
select 2, 'FO', '2G', 2015, 'Diesel', '4x4'
union all
select 3, 'FO', '2H', 2015, 'Diesel', '4x4'
union all
select 4, 'IN', '2G', 2015, 'Diesel', '4x4';
DECLARE @sql Nvarchar(MAX);
WITH cteTally AS(
SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS segval
FROM SYS.all_columns
),
cteRanges AS(
SELECT (@KmRange * (segval - 1)) + SIGN(segval - 1) AS LowerRange,
@KmRange * segval AS UpperRange
FROM cteTally
WHERE segval <= (FLOOR(300000/@KmRange))
)
SELECT @sql = 'select ID, Make, Model,ModelYear, FuelType,Cylinder ' + CHAR(10)
+ ( SELECT CHAR(9) + REPLACE(REPLACE(','''''--',MAX(CASE WHEN something BETWEEN <<LowerRange>> AND <<UpperRange>> THEN SomeValue END)'
+ ' AS "<<LowerRange>>-<<UpperRange>>"'
,'<<LowerRange>>', LowerRange), '<<UpperRange>>', UpperRange) + CHAR(10)
FROM cteRanges
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ 'from #Temp' + CHAR(10)
+ 'GROUP BY ID, Make, Model,ModelYear, FuelType,Cylinder;';
PRINT @sql;
EXEC( @sql);
GO
DROP TABLE #Temp;
May 17, 2017 at 10:24 am
Luis Cazares - Wednesday, May 17, 2017 9:13 AMApparently, I left the code window open from when Jeff deleted the thread and I couldn't post anymore. It changes how you create the the ranges, so I'll post it as general knowledge but I still believe that the pivot should be done in SSRS.
CREATE TABLE #Temp (ID int, Make varchar(2), Model varchar(2), ModelYear int, FuelType varchar(15), cylinder varchar(3) );
DECLARE @KmRange bigint = 5000;
insert into #Temp
select 1, 'FO', '2E', 2015, 'Diesel', '4x4'
union all
select 2, 'FO', '2G', 2015, 'Diesel', '4x4'
union all
select 3, 'FO', '2H', 2015, 'Diesel', '4x4'
union all
select 4, 'IN', '2G', 2015, 'Diesel', '4x4';DECLARE @sql Nvarchar(MAX);
WITH cteTally AS(
SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS segval
FROM SYS.all_columns
),
cteRanges AS(
SELECT (@KmRange * (segval - 1)) + SIGN(segval - 1) AS LowerRange,
@KmRange * segval AS UpperRange
FROM cteTally
WHERE segval <= (FLOOR(300000/@KmRange))
)SELECT @sql = 'select ID, Make, Model,ModelYear, FuelType,Cylinder ' + CHAR(10)
+ ( SELECT CHAR(9) + REPLACE(REPLACE(','''''--',MAX(CASE WHEN something BETWEEN <<LowerRange>> AND <<UpperRange>> THEN SomeValue END)'
+ ' AS "<<LowerRange>>-<<UpperRange>>"'
,'<<LowerRange>>', LowerRange), '<<UpperRange>>', UpperRange) + CHAR(10)
FROM cteRanges
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ 'from #Temp' + CHAR(10)
+ 'GROUP BY ID, Make, Model,ModelYear, FuelType,Cylinder;';
PRINT @sql;
EXEC( @sql);GO
DROP TABLE #Temp;
Yes, I was also thinking to do in reporting services. My idea is to create the range in sql query as rows like I showed in sample DDL above and then do a column grouping on Km Range column and row grouping on the remaining columns in the report design .
I believe it will work.
Thanks,
Charmer
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply