N-Tupling Data from one Table to another table! How?

Question

Hi all,

I have two tables - Table1 and Table2. In Table1 I have a list of companies with performance data. There are ten companies and 10 bit of performance data. So my columns are Id, Company and Value.

In Table2 I have columns - Id, Col_01c, Col_01v, Col_02c, Col_02v ... Col_20c, Col_20v.

What I want to do is to take the ten companies and their values and put them into one row, the company into the ...c and the value into the ...v columns - so I end up with one row from the ten rows.

I beleive this is called N-Tupling. I have seen this done in a Filemaker database, but I want to do this in a SQL Server 2005 database using T-SQL in Management Studio.

Any ideas?

---

Lee Warren

Answer 1

This is a typical pivot operation, and this is a question that is answered a couple of days in this forum.

I will have to assume that the primary of Table1 is (Id, Company) else this gets difficult.

SELECT Id,
       'Company1' AS Col_01c,
       ...
       'Company10' AS Col_10c,
       MIN(CASE WHEN Company = 'Company1' THEN Value END) AS Col_01v,
       ...
       MIN(CASE WHEN Company = 'Company10' THEN Value END) AS Col_10v
FROM   Table1
GROUP  BY Id

The purpose of the MIN operation is to put all values on a single row. Each MIN only sees one non-NULL value, so it would work with MAX as well.

SQL 2005 also offer a special PIVOT operator, but the pattern above ports to other products and is generally easier to master. (Which is testified by the fact that I don't know how to use PIVOT.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hi Erland,

Many thanks for your post. I will  give it a try and post back with the results of how I got on.

Cheers!

Lee

---

Lee Warren

Answer 3

Hi Erland,

I run the following on Table002: (sorry, my column name was Country not Company)

SELECT Id,
       'Country1' AS Col_01c,
       'Country2' AS Col_02c,
       'Country3' AS Col_03c,
       'Country4' AS Col_04c

       MIN(CASE WHEN Company = 'Country1' THEN Value END) AS Col_01v,
       MIN(CASE WHEN Company = 'Country2' THEN Value END) AS Col_02v,
       MIN(CASE WHEN Company = 'Country3' THEN Value END) AS Col_03v,
       MIN(CASE WHEN Company = 'Country4' THEN Value END) AS Col_04v

FROM   Table001
GROUP  BY Id

And I get the following error -

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near 'MIN'.

Any ideas why?

---

Lee Warren

Answer 4

you have missed a comma after this 'Country4' AS Col_04c

you need to use:

'Country4' AS Col_04c,

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 5

haven't run the query, but you're missing a comma after the 'country4' line, and before the MIN

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 6

If I add the comma I get the following:

Msg 207, Level 16, State 1, Line 7

Invalid column name 'Company'.

Msg 207, Level 16, State 1, Line 8

Invalid column name 'Company'.

Msg 207, Level 16, State 1, Line 9

Invalid column name 'Company'.

Msg 207, Level 16, State 1, Line 10

Invalid column name 'Company'.

---

Lee Warren

Answer 7

In your question you mentioned that you have Table with columns:

 So my columns are Id, Company and Value.

It is the same Company Column that is referred in the Query. If the column name in your table is different use that Column Name.

Please post you actual DDL script for others to help you..

---

The simpler the solution the stronger it is
If this post answers you, please mark it as answer..
If this post is useful, please vote it as useful..

Answer 8

change "WHEN COMPANY" TO "WHEN COUNTRY" on each of the 4 lines...  (I presume, based on your comments and the query)

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 9

Columns are Id, Country, Value.

This is what I am using:

SELECT Id, 
       'Country1' AS Col_01c,
       'Country2' AS Col_02c,
       'Country3' AS Col_03c,
       'Country4' AS Col_04c,
       MIN(CASE WHEN Country = 'Country1' THEN Value END) AS Col_01v,
       MIN(CASE WHEN Country = 'Country2' THEN Value END) AS Col_02v,
       MIN(CASE WHEN Country = 'Country3' THEN Value END) AS Col_03v,
       MIN(CASE WHEN Country = 'Country4' THEN Value END) AS Col_04v
  FROM [A510945].[Table001]
GROUP  BY Id

And this is what I get now:

1 Country1 Country2 Country3 Country4 NULL NULL NULL NULL
2 Country1 Country2 Country3 Country4 NULL NULL NULL NULL
3 Country1 Country2 Country3 Country4 NULL NULL NULL NULL
4 Country1 Country2 Country3 Country4 NULL NULL NULL NULL

---

Lee Warren

Answer 10

Here is my Table001:

Id Country Value
1 UK                             10                           
2 France                         20                           
3 Italy                          30                           
4 Spain                          40                           

---

Lee Warren

Answer 11

Remove ID and GROUP BY ID from your query. I assume that ID is the unique column.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 12

Try:

SELECT 
       cast('UK' as varchar(20)) AS Col_01c,
       cast('France' as varchar(20)) AS Col_02c,
       cast('Italy' as varchar(20)) AS Col_03c,
       cast('Spain' as varchar(20)) AS Col_04c,
       MIN(CASE WHEN Country = 'UK' THEN Value END) AS Col_01v,
       MIN(CASE WHEN Country = 'France' THEN Value END) AS Col_02v,
       MIN(CASE WHEN Country = 'Italy' THEN Value END) AS Col_03v,
       MIN(CASE WHEN Country = 'Spain' THEN Value END) AS Col_04v
  FROM [A510945].[Table001]

This code assumes your countries always be the same and never change. Otherwise we need to use dynamic query which is a bit more complex.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 13

Try:

SELECT 
       cast('UK' as varchar(20)) AS Col_01c,
       cast('France' as varchar(20)) AS Col_02c,
       cast('Italy' as varchar(20)) AS Col_03c,
       cast('Spain' as varchar(20)) AS Col_04c,
       MIN(CASE WHEN Country = 'UK' THEN Value END) AS Col_01v,
       MIN(CASE WHEN Country = 'France' THEN Value END) AS Col_02v,
       MIN(CASE WHEN Country = 'Italy' THEN Value END) AS Col_03v,
       MIN(CASE WHEN Country = 'Spain' THEN Value END) AS Col_04v
  FROM [A510945].[Table001]

This code assumes your countries always be the same and never change. Otherwise we need to use dynamic query which is a bit more complex.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Thanks Naomi,

That works fine now. However, the names of the country will change. How do we do that?

---

Lee Warren

Answer 14

In this case we need to generate the above dynamically, e.g.

USE tempdb

CREATE TABLE Productions (
	Id INT
	,Country VARCHAR(20)
	,Value INT
	)

INSERT INTO Productions
VALUES (
	1
	,'UK'
	,10
	)
	,(
	2
	,'France'
	,20
	)
	,(
	3
	,'Italy'
	,30
	)
	,(
	4
	,'Spain'
	,40
	)

DECLARE @SQL NVARCHAR(max)
	,@Cols NVARCHAR(max)
	,@ColsV NVARCHAR(max)

SELECT @Cols = STUFF((
			SELECT ', ' + 'cast(''' + Country + ''' AS VARCHAR(20)) AS [Col_0' + 
convert(VARCHAR(10), Rn) + 'c]'
			FROM (
				SELECT ID
					,Country
					,ROW_NUMBER() OVER (
						ORDER BY ID
						) AS Rn
				FROM Productions
				) src
			ORDER BY ID
			FOR XML PATH('')
				,type
			).value('.', 'nvarchar(max)'), 1, 2, '')

--print @Cols 
SELECT @ColsV = STUFF((
			SELECT ', MAX(CASE WHEN Country =''' + Country + ''' THEN ' + 
cast(Value AS VARCHAR(20)) + ' END) AS [Col_0' + convert(VARCHAR(10), Rn) + 'v]'
			FROM (
				SELECT Value
					,Country
					,ID
					,ROW_NUMBER() OVER (
						ORDER BY ID
						) AS Rn
				FROM Productions
				) src
			ORDER BY Id
			FOR XML PATH('')
				,type
			).value('.', 'nvarchar(max)'), 1, 2, '')

--print @ColsV
SET @SQL = 'SELECT ' + @Cols + ', ' + @ColsV + '
FROM Productions'

--PRINT @SQL

EXECUTE (@SQL)

Replace Productions with your table name in all the statements.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 15

You are on SQL 2005, which does not support the insert approach in the query Naomi gave you.  Just change it to:

INSERT INTO Productions
Select1, 'UK', 10
Union All
Select 2, 'France', 20
Union All
Select 3, 'Italy', 30
Union All
Select 4, 'Spain', 40

In your production system you won't need this part anyway, just the bits after.

Answer 16

I amended the code slightly to this:

USE FILPUBLISHINGSolutions
DECLARE @SQL NVARCHAR(max)
	,@Cols NVARCHAR(max)
	,@ColsV NVARCHAR(max)
SELECT @Cols = STUFF((
			SELECT ', ' + 'cast(''' + Country + ''' AS VARCHAR(20)) AS [Col_0' + convert(VARCHAR(10), Rn) + 'c]'
			FROM (
				SELECT ID
					,Country
					,ROW_NUMBER() OVER (
						ORDER BY ID
						) AS Rn
				FROM Table001
				) src
			ORDER BY ID
			FOR XML PATH('')
				,type
			).value('.', 'nvarchar(max)'), 1, 2, '')
--print @Cols 
SELECT @ColsV = STUFF((
			SELECT ', MAX(CASE WHEN Country =''' + Country + ''' THEN ' + cast(Value AS VARCHAR(20)) + ' END) AS [Col_0' + convert(VARCHAR(10), Rn) + 'v]'
			FROM (
				SELECT Value
					,Country
					,ID
					,ROW_NUMBER() OVER (
						ORDER BY ID
						) AS Rn
				FROM Table001
				) src
			ORDER BY Id
			FOR XML PATH('')
				,type
			).value('.', 'nvarchar(max)'), 1, 2, '')
--print @ColsV
SET @SQL = 'SELECT ' + @Cols + ', ' + @ColsV + '
FROM Table001'
--PRINT @SQL
EXECUTE (@SQL)

And got this:

UK                   France               Italy                Spain                10 20 30 40

Excellent. Many thanks. I just need to go through the code and work out exactly what you did... lol.

THanks.

Lee

---

Lee Warren

Answer 17

Yes, exactly. Just change the original insert statement. You don't even need it, you can go ahead and remove creation of the table, etc. and go directly for select statement using your own table name.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 18

I tested Naomi's script and it runs without error.

I suppose that you adpoted the query to your table, and managed to introduced a syntax error. Now, generally, I expect people to be able to spot trivial syntax errors on their own. It may seem hard, but if you aspire to be an SQL programmer, you better get used to it, because you will encouter those sort of errors again and again.

Now, Naomi's solution is a lot more complex than my original solution, but you also added a very important piece of information.

Now, according to your initial post, the target table has 10 columns for names and 10 for values. Does this mean that you always have 10 countries? In such case, there is a solution which does not require dynamic SQL.

Overall, I would recommend you investigate your error mesages and try to address them on your own. I will have to admit that it is much more rewarding to help someone who is making an effort himself, than someone who just want to be spoonfed a solution.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 19

Thank you all for your very valued input. I am trying to be a better programmer although it is not currently my main function.

Again, thank you all.

Best

Lee

---

Lee Warren

Answer 20

Hi Naomi,

I have been using the query that you have suggested and it works a treat. I am now needing to expand upon this and have been testing over the past few days, but with little success.

What I am trying to do is to get more 'rows' of data. eg:

I want to have each icrs number to be one table row. So in the above example, I should get 3 rows of data.

How do I amend the query to do this? I have had no luck in doing this so far.

Kind regards

Lee

---

Lee Warren