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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy