question

ajay_7879 avatar image
ajay_7879 asked

SQL Query to get Row Tablix Result in Column

My Sql table has a data in this manner. Acct Date Id 2344323 16:05.0 1 2344323 59:11.7 2 2344323 54:09.8 3 4422444 17:44.0 1 Required Output in this manner Acct 1 2 3 2344323 16:05.0 59:11.7 54:09.8 4422444 17:44.0 Please help in getting the desired format through SQL SERVER 2008 Query
sqlserver 2008
10 |1200

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

1 Answer

·
Tom Staab avatar image
Tom Staab answered
It's a little difficult to read your unformatted string of numbers, but I believe your data is like this: Acct Date Id 2344323 16:05.0 1 2344323 59:11.7 2 2344323 54:09.8 3 4422444 17:44.0 1 And your output should be like this: Acct 1 2 3 2344323 16:05.0 59:11.7 54:09.8 4422444 17:44.0 If that is correct, your answer is to use the PIVOT clause. WITH MyData (Acct, [Date], Id) AS ( SELECT 2344323, '16:05.0', 1 UNION SELECT 2344323, '59:11.7', 2 UNION SELECT 2344323, '54:09.8', 3 UNION SELECT 4422444, '17:44.0', 1 ) SELECT Acct, [1], [2], [3] FROM (SELECT Acct, [Date], Id FROM MyData) p PIVOT ( MAX([Date]) FOR Id IN ([1],[2],[3],[4],[5]) ) pvt ORDER BY Acct ; 2 important notes: 1. That will only give you the max value in the Date field for each combination of Acct and Id. 2. You need to hardcode all possible Id values as I did numbers 1 through 5. If that is not possible, there are more complicated dynamic pivot options available if you search the internet. Edit: I attempted to format your data in an HTML table. It looked fine until I posted it, and then the formatting was stripped. I tried again using tab characters in a code block.
1 comment
10 |1200

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

@Tom Staab Looks like the tabs in the original question didn't let the formatting to work properly. I edited the input by replacing the tabs with spaces and then highlighting the block and pressing Ctrl + K.
0 Likes 0 ·

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.