Sql horizontal to vertical table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql horizontal to vertical table

Author

Topic

IanCPFC
Starting Member

1 Post

Posted - 2015-01-07 : 03:55:38

Hi everyone,
I'm new on this forum, and not very expert in sql...
Here's my question. Thanks a lot to anyone willing to help!

I have a table like this:


weight type factory1 factory2 factory3 factory4.... to factory 150

1kg goods 5.00 5.50 5.20 5.00...
2kg goods 6.00 6.20 6.15 6.30...
3kg goods 4.00 4.50 5.00 4.30...
...

and would like to extract data this way:


producer type weight price

factory1 goods 1kg 5.00
factory1 goods 2kg 6.00
factory1 goods 3kg 4.00
factory1.....
then

factory2 goods 1kg 5.50
factory2 goods 2kg 6.20
and so on for all factories.

Hope I could explain.

I tried with UNPIVOT but it does not allow it (I'm using Navicat 8), saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UNPIVOT...".

So I am short on options...

Thanks for helping me!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-07 : 07:13:56

it looks like you're asking a question about mysql. This however is a sql server forum.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-07 : 07:17:25

SQLTeam is a MS SQL Server forum so you will probably be better off checking on a MySQL site.

On saying that, I do not imagine the syntax for UNPIVOT will be much different than MS SQL for later versions of MySQL.

In MS SQL server you would do something like:


-- *** Test Data ***
CREATE TABLE #t
(
	[weight] varchar(20) NOT NULL
	,[type] varchar(20) NOT NULL
	,factory1 decimal(10,2) NOT NULL
	,factory2 decimal(10,2) NOT NULL
	,factory3 decimal(10,2) NOT NULL
	,factory4 decimal(10,2) NOT NULL
);
INSERT INTO #t
SELECT '1kg', 'goods', 5.00, 5.50, 5.20, 5.00
UNION ALL SELECT '2kg', 'goods', 6.00, 6.20, 6.15, 6.30
UNION ALL SELECT '3kg', 'goods', 4.00, 4.50, 5.00, 4.30;
-- *** End Test Data ***

SELECT producer, [type], [weight], price
FROM
(
	SELECT *
	FROM #t
) S
UNPIVOT
(
	price FOR producer IN
	(
		factory1, factory2, factory3, factory4 --..., factory150
	)
) U
ORDER BY producer, [type], [weight];

If your version of MySQL does not support unpivot then the same effect can be produced by use of a number/tally table and the CASE statement.


-- *** Extra Test Data ***
CREATE TABLE #n
(
	n int NOT NULL
);
INSERT INTO #n
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4;
-- ...
-- UNION ALL SELECT 150;
-- *** End Extra Test Data ***

SELECT 
	CASE N.n
		WHEN 1 THEN 'factory1'
		WHEN 2 THEN 'factory2'
		WHEN 3 THEN 'factory3'
		WHEN 4 THEN 'factory4'
		--...
		--WHEN 150 THEN 'factory150'
	END AS producer
	,[type], [weight]
	,CASE N.n
		WHEN 1 THEN factory1
		WHEN 2 THEN factory2
		WHEN 3 THEN factory3
		WHEN 4 THEN factory4
		--...
		--WHEN 150 THEN factory150
	END AS price
FROM #t T, #n N
ORDER BY producer, [type], [weight];

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources