Urgent help needed to convert 100 rows into 100 columns

  • 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

  • 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)

  • 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.

  • 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)

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

  • 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