October 18, 2010 at 10:31 pm
Hi all
I am new to SQL SERVER.
Please help me how to write a cursor/ procedure to convert rows into columns. I am using SQL SERVER 2000.
i am having 3 tables namely emp,dept,joiningdate.
The sample table data looks like
emp table contains columns with values
empid, empname
1 Smith
2 john,
3 david
4 Ann
. .
.
.
.
20 stephen
dept table contains columns with values like
empid, deptid, dept name,value
1 100 Prod 1
2 101 dev 1
3 102 support 1
4 103 sales 1
. . .
. '
.
.
20 120 security 1
and year table contains
deptid, year
100 1900
101 1910
102 1920
103 1930
.
.
120 1950
Now using these 3 tables i want to write a querry to get the result like this.
empname 1900 1910 1920 1930.....................1950
Smith 1 0 0 0....................... 0
john 0 1 0 0....................... 0
david 0 0 1 0....................... 0
Ann 0 0 0 1........................0
Please help me out. Its a urgent requirement.
Thanks & regards
Thejesh Kumar
Application Developer
October 19, 2010 at 12:11 am
for this create dynamic query. a loop that will create a pivot query.
e.g.,
Create table #t1
(
IdInt Identity(1,1),
ProductVarchar(15),
CompanyVarchar(15),
StockInt
)
GO
Insert into #t1
Values('Pen', 'Renold', 100),
('Pen', 'Cello', 100),
('Pen', 'Lexi', 100)
Select Row_Number() Over(order by Company) Id, * into #Comp
From
(
Select Distinct Company From #t1
) A
Declare @FldListVarchar(1000)
Declare @iTinyInt
Declare @CntTinyInt
Declare @CompanyVarchar(15)
Select @Cnt = Max(Id), @FldList = '', @i = 1 from #Comp
While @i <= @Cnt
Begin
Select @Company = Company from #Comp Where Id = @i
Set @FldList = @FldList + '[' + @Company + '],'
Set @i = @i + 1
End
Set @FldList = Left(@FldList, len(@FldList)-1)
Declare @SqlVarchar(1000)
Set @Sql ='
Select Product, ' + @FldList + '
From
(
Select Product, Company, Stock from #t1
) As P
Pivot
(
Sum(Stock)
For Company In (' + @FldList + ')
) As Pvt'
Exec(@Sql)
October 19, 2010 at 2:51 am
How to do it SQL SERVER 2000. ROW_NUMBER(), OVER() doesnt exist in SQL SERVER 2000.
Please help how to do this in sql server 2000.
October 19, 2010 at 3:37 am
Hi,
As of my understading am pasting the scripts.Tell me if it provide solution for you.
USE [SSISDB]
GO
/****** Object: Table [dbo].[Emp] Script Date: 10/19/2010 14:45:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Emp]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Emp](
[EmpId] [int] NULL,
[Ename] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
USE [SSISDB]
GO
/****** Object: Table [dbo].[DeptMap] Script Date: 10/19/2010 14:48:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeptMap]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DeptMap](
[Empid] [int] NULL,
[Deptid] [int] NULL,
[Deptname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[value] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
USE [SSISDB]
GO
/****** Object: Table [dbo].[Deptyear] Script Date: 10/19/2010 14:48:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Deptyear]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Deptyear](
[Deptid] [int] NULL,
[year] [int] NULL
) ON [PRIMARY]
END
GO
insert into dbo.Deptyear
select 100,1900
union all
select 101,1910
union all
select 102,1920
union all
select 103,1930
GO
insert into dbo.DeptMap
select 1,100,'Prod',1
union all
select 2,101,'dev',1
union all
select 3,102,'support',1
union all
select 4,103,'sales',1
GO
insert into dbo.Emp
select 1,'mm'
union all
select 2,'tyut'
union all
select 3,'bhjh'
union all
select 4,'jhg'
union all
select 5,'hjjh'
Go
This is the query Propably you want
declare @sql varchar(4000);
declare @sql1 varchar(200);
declare @sql2 varchar(2000);
set @sql='Select Distinct ename'
declare @year int;
declare c_cursor cursor fast_forward for
select [year] from dbo.Emp e
inner join dbo.DeptMap dm
on e.empid=dm.empid
inner join dbo.Deptyear dy
on dm.deptid=dy.deptid
open c_cursor
fetch next from c_cursor into @year
while @@fetch_status=0
begin
set @sql1=', count(CASE WHEN [year]='+CAST(@year as Varchar)+' Then isnull([value],0) end) AS'+'['+CAST(@year as Varchar)+']'
set @sql=@sql+@sql1;
fetch next from c_cursor into @year
end
close c_cursor
deallocate c_cursor
set @sql2='from dbo.Emp e
inner join dbo.DeptMap dm
on e.empid=dm.empid
inner join dbo.Deptyear dy
on dm.deptid=dy.deptid
group by ename,year,[value]'
set @sql=@sql+@sql1+@sql2;
exec(@sql)
this may be help for writing your query.
But if you will not given scripts as shown no one will help you. keep posting
Exact data you want.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
October 19, 2010 at 4:49 pm
thejask08 (10/19/2010)
How to do it SQL SERVER 2000. ROW_NUMBER(), OVER() doesnt exist in SQL SERVER 2000.Please help how to do this in sql server 2000.
It would help to be mindful of which forum that you are posting in. When you post in a SQL 2008 forum, you get answers for SQL 2008.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 19, 2010 at 11:41 pm
thejask08 ... row_number() is just added to get an id so that we can loop the comany.
so instead of the statemnet below ........
Select Row_Number() Over(order by Company) Id, * into #Comp
From
(
Select Distinct Company From #t1
) A
U can put it this way ......
Create table #Comp
(
Id Int,
Company Varchar(15)
)
Insert into #Comp
Select Distinct Company
October 20, 2010 at 6:53 am
Thanks a lot guys. I did it using crosstab procedure
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
EXECUTE crosstab 'select empname,emp.empid from emp left join dept on (dept.empid=emp.empid)
inner join years on (years.deptid=dept.deptid)
group by empname,emp.empid', 'sum(value)','joiningyear','years'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply