August 23, 2018 at 10:53 am
August 23, 2018 at 2:21 pm
create table #Temp
(
Manufacture varchar(50)
, Cartype varchar(50))
insert into #temp
select 'GM', 'Truck'
union select 'Ford', 'Truck'
union select 'GM', 'Van'
union select 'Dodge', 'SportsCar'
select * from #temp
SELECT
max( CASE WHEN Manufacture = 'Dodge' THEN Cartype END) AS Dodge,
max( CASE WHEN Manufacture = 'Ford' THEN Cartype END) AS Ford,
max( CASE WHEN Manufacture = 'GM' THEN Cartype END) AS GM
FROM #temp
August 23, 2018 at 3:23 pm
Hi,
My issue is that I " don't know the number of Manufacturers or Cartypes - meaning they can fluctuate" There could Fiat or Mercedes for example. So this example won't work unfortunately. 🙁
August 23, 2018 at 3:40 pm
TJ_T - Thursday, August 23, 2018 10:53 AMI need help with SQL PIVOT
ExampleSELECT Manufacturer, CarType
FROM VehiclesManufacture Cartype
Ford SportsCar
GM Truck
Ford Truck
GM Van
Dodge SportsCarI want to pivot or unpivot the table to do this:Ford GM Dodge
SportsCar Truck SportsCar
Truck VanI don't know the number of Manufacturers or Cartypes - meaning they can fluctuate
You need a dynamic Pivot or Cross Tab. Please see the article at the following link for the dynamic Cross Tab with the understanding that you want to use MAX(CASE) as a part of the pivot formulas.
http://www.sqlservercentral.com/articles/Crosstab/65048/
If you were to provide data in a readily consumable format (see the first link under "Helpful Links" in my signature line below for one way to do that), someone may actually help your write the code you need.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2018 at 4:00 pm
Hi thanks,
hmmm … this looks rather complicated. I just need to make column into a row and the second column to stay a column.
August 23, 2018 at 4:58 pm
SELECT Manufacturer, CarType1, CarType2, CarType3
FROM
(
SELECT Manufacturer, CarType,
'CarType' + CAST(ROW_NUMBER() OVER (PARTITION BY Manufacturer ORDER BY MANUFACTURER) as VARCHAR (15)
FROM VEHICLES
) TEMP
PIVOT
MAX(CarType)
FOR COLUMNSEQUENCE IN (CarType1, CarType2, CarType3)
August 24, 2018 at 12:17 am
TJ_T - Thursday, August 23, 2018 4:58 PMThis would work if I knew all of the CarTypes, but I don't. Any ideas?
Yes... try the method in the article you think is "complicated". It's actually quite simple once you've done it a time or two.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2018 at 5:20 am
TJ_T - Thursday, August 23, 2018 4:00 PMHi thanks,hmmm … this looks rather complicated. I just need to make column into a row and the second column to stay a column.
I understand your hesitation, but trust me, follow Jeff's advice and you will be both educated and happy with the results.
Dave
August 25, 2018 at 10:19 am
TJ_T - Thursday, August 23, 2018 4:00 PMHi thanks,hmmm … this looks rather complicated. I just need to make column into a row and the second column to stay a column.
What tool will be used to display/present the data? Ideally - that would be where you pivot the data instead of trying to do all the work in SQL Server. If it must be done in SQL Server then follow Jeff M's advice and go through that article.
If this is going to something like SSRS - then utilize a Matrix object in SSRS to pivot the data which will allow for unknown values to be pivoted. In either case - you really should limit the number of available elements to be pivoted. Too many elements will make viewing/utilizing the output more difficult for the end users.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 25, 2018 at 8:22 pm
TJ_T - Thursday, August 23, 2018 4:00 PMHi thanks,hmmm … this looks rather complicated. I just need to make column into a row and the second column to stay a column.
The real fact is that this type of thing IS a bit complicated and it won't be the last time you ever run across it. You really should read the article and learn how to do "complicated" things.
To whet your appetite for knowledge and to demonstrate that it's pretty easy to do, here's the code that does it, including the right way to build test data for posts in the future so that you get coded answers so that you don't actually have to read an article to learn something new. 😉 I still recommend that you read the article and do it all step by step because there's a shedload of technique in that article that will help you get your next job. At least read the comments in the code because you're actually going to be the person that has to support it.. 😉
--Question Ref: https://www.sqlservercentral.com/Forums/1991307/PIVOT#bm1991586
--=======================================================================================
-- Create the test data.
-- This is NOT a part of the solution. We just need some data to demonstrate the
-- coded solution with.
--=======================================================================================
--===== If the test table already exists, drop it to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly
SELECT *
INTO #TestTable
FROM (
VALUES
('Ford' ,'SportsCar')
,('GM' ,'Truck')
,('Ford' ,'Truck')
,('GM' ,'Van')
,('Dodge','SportsCar')
)v(Manufacture,CarType)
;
--===== Show the content of the new table
SELECT *
FROM #TestTable
;
--=======================================================================================
-- The following code is a solution using a "Dynamic Crosstab"
-- Tech Ref: http://www.sqlservercentral.com/articles/Crosstab/65048/
--=======================================================================================
--===== Declare the variables that will contain the dynamic SQL
DECLARE @SQL1 NVARCHAR(MAX)
,@SQL2 NVARCHAR(MAX)
,@SQL3 NVARCHAR(MAX)
;
--===== If the ColumnName table exists, drop it to make reruns in SSMS easier.
-- You can comment this piece of code out in a production store procedure.
IF OBJECT_ID('tempdb..#ColumnName','U') IS NOT NULL DROP TABLE #ColumnName
;
--===== Create and populate the Control table.
SELECT DISTINCT
ColumnName = Manufacture
INTO #ColumnName
FROM #TestTable
;
--===== Create the first part of the static SQL
SELECT @SQL1 = '
WITH cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Manufacture ORDER BY CarType)
,Manufacture
,CarType
FROM #TestTable
)
SELECT '
;
--===== Create the dynamic CROSS TAB columns code
SELECT @SQL2 = ISNULL(@SQL2+',','')
+ REPLACE(REPLAcE('
<<ColumnName>> = MAX(CASE WHEN Manufacture = "<<ColumnName>>" THEN CarType ELSE "" END)'
,'"','''')
,'<<ColumnName>>',ColumnName)
FROM #ColumnName
ORDER BY ColumnName
;
--===== Create the final static code
SELECT @SQL3 = '
FROM cteEnumerate
GROUP BY RowNum
ORDER BY RowNum;'
;
--===== Let's see the code
PRINT @SQL1+@SQL2+@SQL3
;
--===== Now execute the dynamic SQL to produce the results.
-- Note that the columns are sorted by column name and the data in each column
-- is sorted by CarType for easy readability.
EXEC (@SQL1+@SQL2+@SQL3)
;
There is a way to do it using PIVOT, as well, but I steer clear of PIVOT because of the reasons in the first article on the subject, which you probably won't take the time to study either. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2018 at 8:07 pm
Did that work for you or what???
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2018 at 6:09 am
Yes it worked perfectly, and yes I have to invest more time into learning. Your criticism is well placed.
September 3, 2018 at 8:49 am
TJ_T - Monday, September 3, 2018 6:09 AMYes it worked perfectly, and yes I have to invest more time into learning. Your criticism is well placed.
Thanks. Glad it worked. And thank you for taking my comments correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply