October 23, 2019 at 9:28 pm
I know I've found the answer to this before but I'm having trouble now because every example I've found for how to transpose rows into columns is assuming I am trying to aggregate some part of the data.
Below is a query that will return 3 rows of data with 4 columns in each row. I need 1 row of data with the columns hEntity, sColor, sShape, and iSize where the values shown for sColor, sShape and iSize come from either sValue for sColor and sShape or hValue for iSize. sColor and sShape are string values and stored in sValue in the table where as iSize is a number and thus stored in hValue.
If anyone has a link to an article or something that details how to do this that would be greatly appreciated. Everything I've found so far is trying to do the pivot while also aggregating one or more of the pieces of data and I am simply looking to just transform it and that is it.
SELECT hEntity, sName, sValue, hValue
FROM TABLE
WHERE sName IN('sColor','sShape','iSize')
AND hEntity IN(1)
Kindest Regards,
Just say No to Facebook!October 23, 2019 at 9:48 pm
Are you going to provide DDL & sample data for this?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 23, 2019 at 10:22 pm
You can use the aggregates min or max. You don't have to count or sum.
Max(case when ... then column else '' end)
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
October 24, 2019 at 2:49 pm
This reply has been reported for inappropriate content.
I didn't really think that was necessary since I'm asking a generic question about transposing with Pivot and I also don;t yet have access to the DB where this info is located. Where in the cloud now and so we are at the vendors mercy on access. I know what data should look like base don existing reports and I know its stored the way it is because our support person with the Vendor said so but I have no clue yet on exactly the names of everything and data typos so I'm assuming for example that the field that stores hValue is a numeric data type but knowing this vendor it could be a varchar used to store a number. That said if it means getting help then here you go. The below is something I made up to create the data scenario I will be facing.
A few key things. The field hEntity is a Foreign Key to another table (no idea what its called) and thus the reason why this value is repeated multiple times. For every unique value for hEntiry there are 4 rows where sName will have 1 of 4 possible values and either sValue or hValue will have the value that sName is referring to depending on whether or not its a string value or a number. For example if sName where 'PenaltyType' then hValue would be NULL and sValue might be something like 'Daily'. If sName were something like 'PenaltyAmount' then sValue would be null and hValue would be 50.00 to represent $50.00
CREATE TABLE [dbo].[DUMMYTABLE]( hEntity NUMERIC(18, 0) NULL, sName VARCHAR(64) NULL, sValue VARCHAR(64) NULL, hValue NUMERIC(18,4) NULL) ON [PRIMARY]
GO
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(1,'sName1',NULL,1.0);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(1,'sName2','mytextvalue1',NULL);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(1,'sName3',NULL,2.0);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(1,'sName4','mytextvalue2',NULL);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(2,'sName1',NULL,5.0);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(2,'sName2','mytextvalue1',NULL);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(2,'sName3',NULL,10.0);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(2,'sName4','mytextvalue2',NULL);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(3,'sName1',NULL,2.0);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(3,'sName2','mytextvalue7',NULL);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(3,'sName3',NULL,7.0);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(3,'sName4','mytextvalue99',NULL);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(4,'sName1',NULL,50.50);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(4,'sName2','mytextvalue51',NULL);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(4,'sName3',NULL,9.0);
INSERT INTO [dbo].[DUMMYTABLE] ( hEntity, sName, sValue, hValue)
VALUES(4,'sName4','mytextvalue0',NULL);
Kindest Regards,
Just say No to Facebook!October 24, 2019 at 2:53 pm
Great start. Based on the sample data, what would your desired results look like?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 24, 2019 at 3:22 pm
Pivoting is a form of aggregation though; you can't pivot you're data without aggregating it somehow. That might be using SUM, or count, but it would also be by using MIN or MAX.
Perhaps explaining why aggregation is needed might help. let's take a "simple" dataset that looks like this:
ID EmployeeID EmployeeName ActionType ActionDate
----------- ----------- --------------- ---------- ----------
1 1 Joe Bloggs Started 2010-01-01
2 2 Sarah Green Started 2013-12-03
3 2 Sarah Green Finished 2014-07-09
4 1 Joe Bloggs Finished 2017-07-19
5 3 Lynda Smith Started 2016-03-02
DDL:
CREATE TABLE dbo.SampleTable (ID int IDENTITY,
EmployeeID int,
EmployeeName varchar(15),
ActionType varchar(10),
ActionDate date)
INSERT INTO dbo.SampleTable (EmployeeID,
EmployeeName,
ActionType,
ActionDate)
VALUES(1,'Joe Bloggs','Started','20100101'),
(2,'Sarah Green','Started','20131203'),
(2,'Sarah Green','Finished','20140709'),
(1,'Joe Bloggs','Finished','20170719'),
(3,'Lynda Smith','Started','20160302');
Now, you want too have that data so that you have a Started and Finished column. So, let's do that without aggregation:
SELECT EmployeeID,
EmployeeName,
CASE ActionType WHEN 'Started' THEN ActionDate END AS [Started],
CASE ActionType WHEN 'Finished' THEN ActionDate END AS Finished
FROM dbo.SampleTable;
Which results in the following:
EmployeeID EmployeeName Started Finished
----------- --------------- ---------- ----------
1 Joe Bloggs 2010-01-01 NULL
2 Sarah Green 2013-12-03 NULL
2 Sarah Green NULL 2014-07-09
1 Joe Bloggs NULL 2017-07-19
3 Lynda Smith 2016-03-02 NULL
Well, that isn't right. Everyone, apart from Lynda who hasn't Finished yet, has 2 rows, not 1. That's because the rows haven't been aggregated. They need to be. As soon as we do add aggregation (in this case MAX), then the result is as you expect:
SELECT EmployeeID,
EmployeeName,
MAX(CASE ActionType WHEN 'Started' THEN ActionDate END) AS [Started],
MAX(CASE ActionType WHEN 'Finished' THEN ActionDate END) AS Finished
FROM dbo.SampleTable
GROUP BY EmployeeID,
EmployeeName;
Which gives the result below:
EmployeeID EmployeeName Started Finished
----------- --------------- ---------- ----------
1 Joe Bloggs 2010-01-01 2017-07-19
3 Lynda Smith 2016-03-02 NULL
2 Sarah Green 2013-12-03 2014-07-09
So, as you can see, you can't pivot, without aggregation; as you won't get the result set you are after.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2019 at 3:26 pm
Let me ask you this then. Is there another/better way to handle transposing rows into columns as of SQL 2014 or higher or is PIVOT still the way to go about doing this kind of thing? I'm not stuck on using PIVOT, its just what I understood to be the way to transpose data like this in T-SQL. If there is a better method other then using PIVOT I'm all ears.
Thanks for taking the time to respond.
Kindest Regards,
Just say No to Facebook!October 24, 2019 at 3:32 pm
Even prior to SQL Server 2014 I would have recommended a Cross tab (Like I have done above). PIVOT is quite restrictive and unflexible. A Cross Tab is by far a better method; and i'm pretty sure you've been able to use the method since at least SQL Server 2005.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2019 at 3:34 pm
Use the cross-tab method as outlined by Thom - this does not utilize the PIVOT operator and is much easier to structure, especially when you want to pivot/cross-tab multiple columns.
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
October 24, 2019 at 3:39 pm
I'm guessing that the table is set up as an EAV. There are other ways to get the information, but the fastest will most likely be the CROSS TAB using MAX() or MIN(). Assuming that there is a unique key on hEntity and sName, either one will give you the same results, because there should be exactly one value.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2019 at 4:53 pm
Using my DUMMYTABLE as the example which I just edited because I realized that the last 4 inserts did not use a value of 4 for hEntity
What I'm trying to achieve will result in 4 rows (because there are 4 distinct value for hEntity in DUMMYTABLE) that looks like the below. I hope this better conveys my end goal.
hEntity sName1 sName2 sName3 sName4
------- ----- ------ ------- ------------
1 1.0 MyTextValue 2.0 MytextValue2
2 1.0 MyTextValue1 10.0 MytextValue2
3 2.0 MyTextValue7 7.0 MytextValue99
4 50.50 MyTextVaalue99 9.0 Mytextvalue0
Kindest Regards,
Just say No to Facebook!October 24, 2019 at 5:03 pm
Just wanted to let everyone know who has taken the time to reply that I figured it out with everyone help. What had me hung up with PIVOT was the whole part about using an aggregate. Once I realized (thanks to several recommendations in this thread) that a Cross Tab was the better route I then was having trouble with how to deal with teh fact that the value I needed to show in each column would be either hValue or sValue and every example so far always assumed a fixed value and not choosing between one of 2 possible columns.
I finally solved this using a variation on what Thom A provided. Below is the final query that so far is working but if anyone has any suggestions on improving it please say so.
Thank yoo
SELECT hEntity,
MAX(CASE sName WHEN 'sName1' THEN ISNULL(sValue,CONVERT(VARCHAR,hValue)) END) AS 'sName1',
MAX(CASE sName WHEN 'sName2' THEN ISNULL(sValue,CONVERT(VARCHAR,hValue)) END) AS 'sName2',
MAX(CASE sName WHEN 'sName3' THEN ISNULL(sValue,CONVERT(VARCHAR,hValue)) END) AS 'sName3',
MAX(CASE sName WHEN 'sName4' THEN ISNULL(sValue,CONVERT(VARCHAR,hValue)) END) AS 'sName4'
FROM DUMMYTABLE
GROUP BY hEntity
Kindest Regards,
Just say No to Facebook!Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply