Database

Create table tblMatrix(Column1 int,Column2 char,Column3 int)

Create Procedure sp_Transpose As
DECLARE @SQLQuery AS NVARCHAR(2000)
DECLARE @ColumnName Nvarchar(50)
DECLARE @AddedColumns Nvarchar(500)
DECLARE @AddedValues Nvarchar(500)
DECLARE @Col1Val Nvarchar(50)
DECLARE @Col2Val Nvarchar(50)
DECLARE @Val2Add Nvarchar(50)

declare NavigatorRecords cursor for
SELECT DISTINCT(COLUMN2) FROM TBLMATRIX

open NavigatorRecords 
fetch next from NavigatorRecords into @ColumnName 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTemp]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table dbo.tblTemp

SET @SQLQuery = 'CREATE TABLE tblTemp(Col Int,'
SET @AddedColumns = 'Col,'
SET @AddedValues = ''
SET @Val2Add = ''

while(@@FETCH_STATUS = 0) 
begin
SET @AddedColumns = @AddedColumns + @ColumnName
SET @SQLQuery = @SQLQuery + @ColumnName + ' ' + 'NVARCHAR(50)'
fetch next from NavigatorRecords into @ColumnName 
IF (@@FETCH_STATUS = 0)
begin
SET @SQLQuery = @SQLQuery + ','
SET @AddedColumns = @AddedColumns + ','
end
end

SET @SQLQuery = @SQLQuery + ')'
EXEC sp_executesql @SQLQuery 

close NavigatorRecords
deallocate NavigatorRecords


declare CursorI cursor for
SELECT DISTINCT(COLUMN1) FROM TBLMATRIX

open CursorI 
fetch next from CursorI into @Col1Val

while(@@FETCH_STATUS = 0) 
begin
SET @AddedValues = @Col1Val + ','
declare CursorJ cursor for
SELECT DISTINCT(COLUMN2) FROM TBLMATRIX

open CursorJ 
fetch next from CursorJ into @Col2Val

while(@@FETCH_STATUS = 0) 
begin
set @Val2Add = '0'
select @Val2Add = Isnull(column3,'0') from tblMatrix where column1 = @Col1Val and column2 = @Col2Val
SET @AddedValues = @AddedValues + @Val2Add
fetch next from CursorJ into @Col2Val
IF (@@FETCH_STATUS = 0)
SET @AddedValues = @AddedValues + ','
end

SET @SQLQuery = 'INSERT INTO tblTemp(' + @AddedColumns +') values(' + @AddedValues +')'
EXEC sp_executesql @SQLQuery 

close CursorJ
deallocate CursorJ

fetch next from CursorI into @Col1Val
end
close CursorI
deallocate CursorI

Select * from tblTemp

Go

Exec sp_transpose 

Regards
J O H N

"Even eagles need a push." David McNally

---

prubyholl wrote:

is it posible to mount .mdf files ( sql 2005 ) in sql 2000 maybe personal edition

SELECT product, local_expect_cost, max(date_entered) <br />
<br />
FROM podetm as mainDetail, poheadm as mainHeader<br />
<br />
WHERE mainDetail.order_no=mainHeader.order_no and date_entered=(SELECT max(date_entered) FROM poheadm as subHeader, podetm as subDetail WHERE subHeader.order_no=subDetail.order_no and subDetail.product=mainDetail.product)<br />
<br />
GROUP BY product, local_expect_cost

select t.product
      ,t.last_date
      ,pod.local_expect_cost
  from ( select pod.order_no
               ,pod.product
               ,max(poh.date_entered) last_date
           from poheadm poh
               ,podetm pod
          where poh.order_no = pod.order_no
          group by pod.order_no, pod.product ) t
       ,podetm pod
 where pod.order_no = t.order_no
   and pod.product = t.product

select t.product
      ,t.last_date
      ,pod.local_expect_cost
  from ( select pod.product
               ,max(poh.date_entered) last_date
           from poheadm poh
               ,podetm pod
          where poh.order_no = pod.order_no
          group by pod.product ) t
       ,poheadm poh
       ,podetm pod
 where poh.date_entered = t.last_date
   and poh.order_no = pod.order_no
   and pod.product = t.product