February 7, 2013 at 3:19 am
Hi all!
Is there an easy way to pivot data into another table, when the design of the first table is something like this:
ColumnProperty (nvarchar(nn))ColumnValue (sql_variant)
=========================
Name Firebird
Color Red
Size 22
Owner JeffK
.. ..
The second table I would like to have this design, with an option to update it, if the values change:
NameColorSizeOwner..
====================
FirebirdRed22Jeffk..
After joining against the first table, and an update has been excecuted (Size for Firebird has changed):
NameColorSizeOwner..
====================
FirebirdRed150JeffK..
StingerBlue22Nadia..
I know the first table has a funny design, but that's just the way it is, and what I have to work with.
Very thankful for help!
Sincerely,
Gord
February 7, 2013 at 5:46 am
I'm going to hope that your "odd" table has a key that allows you to see the start and end of each record, otherwise it is a disaster waiting to happen.
As for your question, can't you just use a view?
-- CREATE SAMPLE DATA BASED ON YOUR POST
IF object_id('dbo.yourTable') IS NOT NULL
BEGIN
DROP TABLE dbo.yourTable;
END;
SELECT [ColumnProperty], CAST([ColumnValue] AS sql_variant) AS [ColumnValue]
INTO dbo.yourTable
FROM (VALUES('Name','Firebird'),('Color','Red'),('Size','22'),('Owner','JeffK')
)a([ColumnProperty],[ColumnValue]);
GO
IF object_id('pivotedTable') IS NOT NULL
BEGIN
DROP VIEW pivotedTable;
END;
GO
CREATE VIEW pivotedTable AS
SELECT
MAX(CASE WHEN [ColumnProperty] = 'Name' THEN [ColumnValue] END) AS Name,
MAX(CASE WHEN [ColumnProperty] = 'Color' THEN [ColumnValue] END) AS Color,
MAX(CASE WHEN [ColumnProperty] = 'Size' THEN [ColumnValue] END) AS Size,
MAX(CASE WHEN [ColumnProperty] = 'Owner' THEN [ColumnValue] END) AS [Owner]
FROM dbo.yourTable;
GO
--== TEST ==--
SELECT *
FROM pivotedTable;
GO
--== UPDATE VALUE ==--
UPDATE dbo.yourTable
SET [ColumnValue] = '150'
WHERE [ColumnProperty] = 'Size';
GO
--== TEST ==--
SELECT *
FROM pivotedTable;
GO
February 7, 2013 at 6:01 pm
GordonLiddy (2/7/2013)
Hi all!Is there an easy way to pivot data into another table, when the design of the first table is something like this:
ColumnProperty (nvarchar(nn))ColumnValue (sql_variant)
=========================
Name Firebird
Color Red
Size 22
Owner JeffK
.. ..
The second table I would like to have this design, with an option to update it, if the values change:
NameColorSizeOwner..
====================
FirebirdRed22Jeffk..
After joining against the first table, and an update has been excecuted (Size for Firebird has changed):
NameColorSizeOwner..
====================
FirebirdRed150JeffK..
StingerBlue22Nadia..
I know the first table has a funny design, but that's just the way it is, and what I have to work with.
Very thankful for help!
Sincerely,
Gord
Three questions:
1. Are the four values associated with "Stinger" present in your first table along with the 4 properties for "Firebird?"
2. When you say "The second table I would like to have this design, with an option to update it," does this mean you want to perform the update against the second table and have the results reflected back in the first table?
3. Is there an additional column in the first table that distinguishes the 4 rows of Firebird from the 4 rows of Stinger?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 8, 2013 at 1:58 am
Thank you very much for your answers, guys!
@cadavre: What do you mean when you say "key that allows to see the start and end.."?
The table with the odd design will not contain more rows. It's primary use is to present the data in an "unpivoted fashion". Yes, it's a strange design, but it solves some other issues we have ...
Thanks for your solution with MAX(CASE ..). I will use it to pivot the data, and then insert it in a table with better design, using proper column names and data types.
@dwain.c
1. No. Firebird and Stinger values are collected into table #2 from separate versions of table #1. Table #2:s purpose is to collect and summarize the data from all other table #1:s that exists on different servers, about 40 of them.
2. No, the update will only be intended for table #2, based on the data in table #1.
Table #1 will be updated, but by another process.
3. No, unfortunately not, but I could create an IDENTITY column, but I don't think it will make much difference, for our purpose.
Thank you for taking your time and effort with this!
Sincerely,
Gord
February 8, 2013 at 5:03 am
GordonLiddy (2/8/2013)
Thank you very much for your answers, guys!@cadavre: What do you mean when you say "key that allows to see the start and end.."?
The table with the odd design will not contain more rows. It's primary use is to present the data in an "unpivoted fashion". Yes, it's a strange design, but it solves some other issues we have ...
Thanks for your solution with MAX(CASE ..). I will use it to pivot the data, and then insert it in a table with better design, using proper column names and data types.
What I mean is, say you've got this: -
-- CREATE SAMPLE DATA BASED ON YOUR POST
IF object_id('dbo.yourTable') IS NOT NULL
BEGIN
DROP TABLE dbo.yourTable;
END;
SELECT [ColumnProperty], CAST([ColumnValue] AS sql_variant) AS [ColumnValue]
INTO dbo.yourTable
FROM (VALUES('Name','Firebird'),('Color','Red'),('Size','22'),('Owner','JeffK'),
('Name','Waterfrog'),('Color','Green'),('Size','250'),('Owner','TomT')
)a([ColumnProperty],[ColumnValue]);
ColumnProperty ColumnValue
-------------- --------------
Name Firebird
Color Red
Size 22
Owner JeffK
Name Waterfrog
Color Green
Size 250
Owner TomT
How do you differentiate between the two different records that are contained? If there was a key, e.g.
RecordKey ColumnProperty ColumnValue
----------- -------------- -------------
1 Name Firebird
1 Color Red
1 Size 22
1 Owner JeffK
2 Name Waterfrog
2 Color Green
2 Size 250
2 Owner TomT
Then this is a workable, if silly, design. Otherwise, you have an unworkable solution because you can never know which record to update.
Say we've got no key: -
ColumnProperty ColumnValue
-------------- --------------
Name Firebird
Color Red
Size 22
Owner JeffK
Name Waterfrog
Color Green
Size 250
Owner TomT
I want to update the size for Firebird - how can I do it? Remember, SQL Server has no concept of row order, so you can't say that the first "Size" in the above list is anything to do with Firebird.
February 8, 2013 at 5:21 am
Ah .. I get your point, and totally agree with what you're saying!
In this case, there will be no more Names or Sizes in the that table. All values will be unique, since it is meant to collect information about a system that is unique in itself ...
I probably should enter a number column anyway, and use as a clustered index, with gaps in it if another, new, value should be squeezed in between.
It gets weirder and weirder, I know 😀
Sincerely,
Gord
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply