question

Ayan Basu avatar image
Ayan Basu asked

Query to return a matrix

I have a table called Test1 with single column. SELECT * FROM test1 Output: A B C D E F G H I need a query with the following output(without using any variable): A B C D E F G H
selectdenormalize
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You're going to need to EDIT your post to include some code blocks or pre blocks so we can see the before and after. I'm going to guess that what you want is PIVOT.
0 Likes 0 ·
Why do you want not to use a variable?
0 Likes 0 ·

1 Answer

· Write an Answer
GPO avatar image
GPO answered
WITH tbl as ( SELECT 'A' as col UNION ALL SELECT 'B' as col UNION ALL SELECT 'C' as col UNION ALL SELECT 'D' as col UNION ALL SELECT 'E' as col UNION ALL SELECT 'F' as col UNION ALL SELECT 'G' as col ) , tbl_with_my_row_id as ( SELECT col ,row_number() OVER(order by col) % 3 as my_subset_id ,ROW_NUMBER() OVER(order by col) as my_row_id FROM tbl ) SELECT t1.col new_col_1 ,t2.col as new_col_2 ,t3.col as new_col_3 FROM tbl_with_my_row_id t1 LEFT JOIN tbl_with_my_row_id t2 ON t1.my_subset_id = t2.my_subset_id - 1 and t1.my_row_id = t2.my_row_id - 1 LEFT JOIN tbl_with_my_row_id t3 ON t1.my_subset_id = t3.my_subset_id + 1 and t2.my_row_id = t3.my_row_id - 1 WHERE t1.my_subset_id = 1
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.