# Convert multiple matrices in one table to multiple row vectors using SQL

• How would I use SQL to present data in tableA into a row vector here is an example:

Table A

type x1 x2 x3

A 4 6 9

A 7 4 1

A 9 6 2

B 1 3 8

B 2 7 9

I am looking for code that would convert to the following

type x1 x2 x3 x1' x2' x3' x1'' x2'' x3''

A 4 6 9 7 4 1 9 6 2

B 1 3 8 2 7 9

• Posting the schema of the table will be helpful. How do you determine the order of the rows?

Don Simpson

• shawn 20335 (4/22/2016)

How would I use SQL to present data in tableA into a row vector here is an example:

Table A

type x1 x2 x3

A 4 6 9

A 7 4 1

A 9 6 2

B 1 3 8

B 2 7 9

I am looking for code that would convert to the following

type x1 x2 x3 x1' x2' x3' x1'' x2'' x3''

A 4 6 9 7 4 1 9 6 2

B 1 3 8 2 7 9

You're looking for pivot or cross tabs. I recommend to use the later.

The problem is that you don't have anything to define the order of rows, so we need to create it.

Here's an example:

`WITH TableA AS(`

` SELECT *`

` FROM (VALUES('A', 4, 6, 9),`

` ('A', 7, 4, 1),`

` ('A', 9, 6, 2),`

` ('B', 1, 3, 8),`

` ('B', 2, 7, 9))x(type, x1, x2, x3)`

`),`

`cteRowNums AS(`

` SELECT *, ROW_NUMBER() OVER( PARTITION BY type ORDER BY (SELECT NULL)) rn`

` FROM TableA`

`)`

`SELECT type,`

` MAX( CASE WHEN rn = 1 THEN x1 END) AS [x1],`

` MAX( CASE WHEN rn = 1 THEN x2 END) AS [x2],`

` MAX( CASE WHEN rn = 1 THEN x3 END) AS [x3],`

` MAX( CASE WHEN rn = 2 THEN x1 END) AS [x1'],`

` MAX( CASE WHEN rn = 2 THEN x2 END) AS [x2'],`

` MAX( CASE WHEN rn = 2 THEN x3 END) AS [x3'],`

` MAX( CASE WHEN rn = 3 THEN x1 END) AS [x1''],`

` MAX( CASE WHEN rn = 3 THEN x2 END) AS [x2''],`

` MAX( CASE WHEN rn = 3 THEN x3 END) AS [x3'']`

`FROM cteRowNums`

`GROUP BY type;`

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• The actual example has 33626 rows and 18 columns, it doesn't seem like using the values function is feasible with the data size. Am i misunderstanding?

I am just a hacker trying to solve a specific problem.

Thank you!

-S

• The values clause is used to have the sample data. In your actual query, you don't have to define the table again. I had to use it have something to code against.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Got it! Thank you! I will do some testing.

Viewing 6 posts - 1 through 5 (of 5 total)