Converting Rows into Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting Rows into Column

Author

Topic

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 00:49:57

Hi Friends,
Can you please, tell me how to convert rows into Columns (SQL Server 2005)

Tanks

Vidhu

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-13 : 01:23:12

Read about PIVOT and UNPIVOT in BOL

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 01:35:15

With the Help of PIVOT Only Fixed rows Can be Converted into Columns..
In my case, i cant use fixed values for converting into row...

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

here [0], [1], [2], [3], [4] these are rows... but i dnt want to mention these values for converting into columns.
if i hve 40 rows in my table.. all the 40 row should be converted as columns....

Vidhu

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-13 : 01:38:50

Check these links you might use your logic

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 01:50:42

i think it gotta help me reallli...
will let u knw after tryin...

Vidhu

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 01:58:04

You hve got the point in this link....

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

in my project... wat we are doing is we are using CODE GENERATOR.. which will generate the DAL File.. when ever we communicate to back end, we need to use this Structure to create a DAL file.. our CODE GENERATOR supports only Table and view.. it doesnt support Stored Procedure..

In the above mentioned link they have used the stored procedure.. well, its working fine... to make use of this concept in my project.. i've to go for a view concept.. which is y i have been searching for how to execute a stored procedure in a view......
Can you please help me in this regard :(

Vidhu

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-13 : 04:55:52

quote:
Originally posted by vidhya.smarty

our CODE GENERATOR supports only Table and view..

Vidhu

Here you have limitation then how we can overcome?

FYI

View Supports only Select statements!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 05:53:28

s.. I knw View supports only select Statement..
So, do you have any idea to over come this situations??

Vidhu

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-05-13 : 05:58:07

SELECT Column_name,
SUM(CASE WHEN Column_name= 'A' THEN qty END) AS A,
SUM(CASE WHEN Column_name= 'B' THEN qty END) AS B,
SUM(CASE WHEN Column_name = 'C' THEN qty END) AS C,
SUM(CASE WHEN Column_name = 'D' THEN qty END) AS D
FROM Table_name
GROUP BY Column_name

KaShYaP

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-05-13 : 05:58:23

i think it works for you

KaShYaP

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-13 : 06:00:42

quote:
Originally posted by kashyapsid

SELECT Column_name,
SUM(CASE WHEN Column_name= 'A' THEN qty END) AS A,
SUM(CASE WHEN Column_name= 'B' THEN qty END) AS B,
SUM(CASE WHEN Column_name = 'C' THEN qty END) AS C,
SUM(CASE WHEN Column_name = 'D' THEN qty END) AS D
FROM Table_name
GROUP BY Column_name

KaShYaP

Not suitable,
We must pivot the rows as dynamic columns not like you mentioned!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-13 : 06:03:43

quote:
Originally posted by vidhya.smarty

s.. I knw View supports only select Statement..
So, do you have any idea to over come this situations??

Vidhu

Solution must be in your project level, change the algorithm to accommodate your requirement!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-05-13 : 06:06:31

ok

KaShYaP

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 06:07:23

Just thinking about it...
I dont have any algorithm as you mentioned..
:(

Vidhu

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-05-13 : 06:10:17

quote:
Originally posted by vidhya.smarty

Just thinking about it...
I dont have any algorithm as you mentioned..
:(

Vidhu

he said to me

KaShYaP

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-13 : 06:11:27

I want clarify something!

Your Stored procedure will have a select statement with dynamic pivot?

If Yes,

Create a view with the select statement inside your SP.

As you said "CODE GENERATOR supports only Table and view"

You can also use temp tables.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 06:13:11

create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as

declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')

create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'

this is the way to create and execute the Dynamic PIVOT...

Vidhu

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-05-13 : 06:17:52

where did you get the procedure can you give me the link

KaShYaP

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-13 : 06:18:17

Can You Use Temp table?

Push the result set of you SP into a #temp table, and use the #temp table for your business.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vidhya.smarty
Starting Member

32 Posts

Posted - 2010-05-13 : 06:26:55

@kasyap...
If your sincerely following this Forum.. you will not have this question right now..

Vidhu

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-13 : 06:29:01

cool !

Is it possible to use temp table?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources