Columns and Rows
-
How do I take a table and turn the columns into rows in a diferent table.
I remember an SP that did something like this.
Any ideas?
-
Hello,
I have one stored procedure ready with me for onverting a row to column for cross Tab reports in MS SQL.
Please find the attached text file for the stored procedure with example in the same.
Regards,
Mahesh
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[crosstab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[crosstab]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Example
Here's two you can run in the pubs database:
EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title', 'sum(qty)','stor_id','stores'
EXECUTE crosstab 'select pub_name, count(qty) as orders, sum(qty) as total
from sales inner join titles on (sales.title_id=titles.title_id)
right join publishers on (publishers.pub_id=titles.pub_id)
group by pub_name', 'sum(qty)','type','titles'
Here's one that will run in Northwind:
EXECUTE crosstab 'SELECT LastName FROM Employees INNER JOIN Orders
ON (Employees.EmployeeID=Orders.EmployeeID)
GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'
-
Here's another example
/*-----------------------------------------------------------------------------------------------
This script tracks the rows in a database over time.
The base table used is #TempRowCount, which has new records added everytime the script is run.
A dynamic query based on that table will display all tables that have had their row count changed since the last
time the script was run.
*/-----------------------------------------------------------------------------------------------
if object_ID('tempdb..#TempRowCount') is null
begin
create table #TempRowCount (TableName varchar(255), RowCnt int, BatchID int, Instance datetime)
end
declare @BatchID int,
@stQuery varchar(8000)
declare @TempRowCount Table (TableName varchar(255), RowCnt int, BatchID int, Instance datetime)
/*--------------------------------------------
Update Base Table with rowcount figures
truncate table #TempRowCount
*/--------------------------------------------
select top 1
@BatchID = BatchID + 1
from #TempRowCount (nolock)
order by BatchID desc
IF @BatchID is null set @BatchID = 1
--Using temp table to store date before inserting into permanent table (query was hanging due to feedback)
Insert @TempRowCount
Select so.name, convert(int, sc.rowcnt) as RowsInTable, @BatchID, getdate()
From sysobjects so (nolock)
JOIN sysindexes sc (nolock) on so.id = sc.id
WHERE sc.indid < 2 and so.Name <> '#TempRowCount'
--Update permanent table
Insert #TempRowCount
Select * from @TempRowCount
/*--------------------------------------------
Create Query to turn batches into columns (the name of the column is the time it ran)
*/--------------------------------------------
declare @Query Table (BatchId int, instance datetime)
Insert @Query
Select distinct batchid, instance
From #TempRowCount (nolock)
--begin Select clause
select @stQuery = 'Select t.TableName, t.RowCnt ',
@BatchID = 0
--Finish Select clause
While 1 = 1
Begin
select @BatchID = BatchID,
@stQuery = @stQuery + ', (t' + cast(BatchID as varchar) + '.RowCnt - t.RowCnt) [' + convert(varchar, instance, 108) + ']'
From @Query
Where BatchID > @BatchID
order by batchid
If @@rowcount = 0 Break
end
--begin from clause
select @stQuery = @stQuery + ' From #TempRowCount t ',
@BatchID = 0
--Finish from clause
While 1 = 1
Begin
select @BatchID = BatchID,
@stQuery = @stQuery + ' LEFT JOIN #TempRowCount ' + 't' + cast(BatchID as varchar) +
' (nolock) on (t.TableName = t' + cast(BatchID as varchar) + '.TableName and t' + cast(BatchID as varchar) + '.Batchid = ' + cast(BatchID as varchar) + ')'
From @Query
Where BatchID > @BatchID
order by batchid
If @@rowcount = 0 Break
end
--Add where clause
set @stQuery = @stQuery + ' Where t.BatchID = 1 and (t' + cast(@BatchID as varchar) + '.RowCnt - t.RowCnt) <> 0'
exec (@stQuery)
Signature is NULL
-
Hi All,
I've found the code posted by Mahesh in 2 places on the web and neither of them work. I've copied and pasted the Create Procedure code into the Pubs (and Northwind) database and have the same problem in both. When I run the examples above, the sp generates invalid sql like this:
SELECT LastName ,
'1996' = count(lastname END),
'1997' = count(lastname END),
'1998' = count(lastname END)
FROM Employees INNER JOIN Orders ON (Employees.EmployeeID=Orders.EmployeeID)
GROUP BY LastName
Notice the END inside the Count() function. Can anyone explain why this doesn't work for me? I figure it must work for someone or you'd have heard by now.
Thanks!
Jim
-
Jim,
We'll need a sample of your data plus the sp (if changed). Also, who's method are you using?
cl
Signature is NULL
-
Thanks for the quick reply. I'm using the stock Pubs database and the code posted above by Mahesh with no changes. If I copy the code above, paste it into Query Analyzer to create the sp and run the example I get the result I posted above.
Thanks!
-
I've been looking at this and here's what I've found so far. The
tempdb.information_schema.columns table does not have any information about the ##pivot table. This is causing @delim to be null and the part that builds the CASE is not executed. I don't really know why tempdb.information_schema.columns would not have the ##pivot table. Any ideas?
-
I realize Mahesh's proc is more generic, but it doesn't appear to be working properly. I'm not to interested in debugging it, though, too tell you the truth.
He's using the same technique I am, just more generalized. I ran my pivot table script, and it worked just fine. I'd suggest you start with that and try to build your own.
cl
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply