Convert horizontal table to vertical in Sql
-
I have the following table format:
need to convert this table to following format:I looked for PIVOT function in other questions, but the "Key" values in input table is not a fixed set of values, they can be anything. I also looked for other such questions but I am not sure in my case, how the query should be written.
Any help will be appreciated, thanks!
my code is
SELECT ROW_NUMBER() OVER (ORDER BY RouteCode) As SrNo,RouteCode as X,
sum(Units) as Y
FROM [INTERFACE_ok].[dbo].[v_A40OrdersBhQt]
where [DeliveryDate] > CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME) and CustomerCode LIKE '900%'
GROUP BY [RouteCode] -
Are the N1–N7 column headings fixed or dynamic?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Sounds like you need to create some dynamic SQL.
There are a few articles on this:
https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query
https://social.technet.microsoft.com/wiki/contents/articles/39000.sql-script-convert-rows-to-columns-using-dynamic-pivot-in-sql-server.aspx - ShawnBryan - Monday, July 23, 2018 10:33 AMI have the following table format:
need to convert this table to following format:I looked for PIVOT function in other questions, but the "Key" values in input table is not a fixed set of values, they can be anything. I also looked for other such questions but I am not sure in my case, how the query should be written.
Any help will be appreciated, thanks!
my code is
SELECT ROW_NUMBER() OVER (ORDER BY RouteCode) As SrNo,RouteCode as X,
sum(Units) as Y
FROM [INTERFACE_ok].[dbo].[v_A40OrdersBhQt]
where [DeliveryDate] > CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME) and CustomerCode LIKE '900%'
GROUP BY [RouteCode]You're confusing a database with a spreadsheet. Databases are strongly typed, whereas spreadsheets are not. This means that the database needs to have one single data type for all fields in column, whereas a spreadsheet can freely mix data types in a single column. You are trying to mix string and integer data types in the same column, that is, you're trying to model a spreadsheet. While you can force the fields to the same data type, it's not advisable. You should look at doing this in your reporting software instead of in T-SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hi Phil its dynamic.
- ShawnBryan - Monday, July 23, 2018 1:07 PM
I agree with Drew's response. This is a job for a reporting tool.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply