Sql row to column

I have two columns as shown below

ColA       ColB
Cash      100
Check     200
Card       300

I want result as

Cash    Check  Card
100      200      300


Here colA has run time values so i can't make it hard code



RETAILREALMAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
I think the links got crossed above, but PIVOT is good to use as suggested if you list is short and consistent as shown above as you will have to hardcode the column names or combine approaches with what Matthew showed for creating dynamic SQL.


SELECT [Cash],[Check],[Card]
FROM TableName
PIVOT (SUM(ColB) FOR ColA IN ([Cash],[Check],[Card])) pvt

Open in new window

0
 
Patrick MatthewsConnect With a Mentor Commented:
DECLARE @sql varchar(MAX)

SELECT @sql = @sql + ',(SELECT SUM(ColB) FROM SomeTable WHERE ColA = ''' + ColA + ''') AS [' + ColA + ']'
FROM SomeTable
GROUP BY ColA
ORDER BY ColA

SET @sql = 'SELECT ' + SUBSTRING(@sql, 2, LEN(@sql))

EXEC(@sql)
0
 
Sander StadConnect With a Mentor Systeemontwikkelaar, Database AdministratorCommented:
With the little information you gave I can only say that you could probably use the PIVOT operator to get the values the way you want.
Here are some websites that can help you:
http://www.dotnetspider.com/resources/20286-PIVOT-Operator-SQL-Server.aspx
http://articles.techrepublic.com.com/5100-10878_11-6143761.html


0
 
Kevin CrossChief Technology OfficerCommented:
Note: PIVOT works for SQL 2005 and above; Matthew's approach works on SQL 2000 as well.  The only thing I would change is one first occurrence you want to check if NULL.
SELECT @sql = IsNull(@sql + ',', '') + '(SELECT SUM(ColB) FROM SomeTable WHERE ColA = ''' + ColA + ''') AS [' + ColA + ']'
FROM SomeTable
GROUP BY ColA
ORDER BY ColA

Open in new window

0
 
RETAILREALMAuthor Commented:
Thanks guys for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.