April 22, 2016 at 10:44 am
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
April 22, 2016 at 12:29 pm
Posting the schema of the table will be helpful. How do you determine the order of the rows?
Don Simpson
April 22, 2016 at 12:59 pm
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;
April 22, 2016 at 6:39 pm
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
April 22, 2016 at 8:18 pm
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.
April 22, 2016 at 8:22 pm
Got it! Thank you! I will do some testing.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply