Convert values from 2 colums into rows T-SQL

Question

I have a problem that i am not able to fix.

I have a table like this

i want the output to be like this (NOT this, look at the revised result image):

is this possible with T-SQL? maybe with Excel?

There are 140 categories and approx. 60.000 rows.

Edit:

Just to clarify. I want an amount column for every category column. So if there are 140 categories, i want 140 amount columns. If there is no amount for a given category, it just going to null or amount =0.

EDIT2: Revised result:

Are you sure that this is what you want? This seems to be a pretty typical case for a simple pivot table where the categories are shown in separate columns. So, you'd have a column `cat1` with the value 132 and a column `cat2` with a value of 413, column `cat3` with a value of 1245, etc. But the picture of your expected outcome seems to differ from that and expects a separate "amount" column. What do you want to show in these columns? — Ralph, May 06 '17 at 14:31
Hi Ralph. I want an amount column for each category column. So if there are 140 categories, i want 140 amounts horizontally. — uba2012, May 06 '17 at 14:36
But you are showing one column for `cat2`. So, in that column I'd expect the value for `cat2`. Hence, a pivot table would show in the `cat2` column the value 132. But what do you want to show in the column `amount` next to `cat2`? Once again, please verify that you are not looking for a solution like this one instead: http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server OR this https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Ralph, May 06 '17 at 14:39
In this case you'll have to do it in Excel with VBA as this is **not** possible in T-SQL. This is due to the fact that all column names in T-SQL must be unique per table. This condition is not met with your specific requirements as you'll have **two** columns for each category: the first column will repeat the categories name and the second (adjacent) column will show the value for the given category. So, the columns `category` and `amount` will repeat as many times as there are categories in your table. Hence, you'll have to program a solution with VBA. — Ralph, May 06 '17 at 15:09
What if the column names where like Category_cat1, Category_cat2 etc. — uba2012, May 06 '17 at 15:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/143570/discussion-between-ralph-and-uba2012). — Ralph, May 06 '17 at 15:14
@Ralph the question doesn't say anything about creating a table of this structure. A select result can have duplicate column names. — Martin Smith, May 06 '17 at 17:53

score 0 · Answer 1 · answered May 06 '17 at 14:52

You can use pivot and dynamic query to generate into columns.. I just gave a sample with 2 categories you can generate the complete list using your table

select * from #yourcustomer
pivot (sum(amount) for category_name in ([cat1],[cat2] --,all categories list generated based on yourtable..........
)) p

Convert values from 2 colums into rows T-SQL

1 Answers1