Rows to Columns in SQL Server 2000

Rows to Columns in SQL Server 2000

Since SQL Server 2000 does not have the PIVOT function, you should be able to use something similar to the following:

DECLARE @query AS NVARCHAR(4000)
DECLARE   @rowCount as int
DECLARE   @pivotCount as int
DECLARE   @pivotRow as varchar(10)

set @rowCount = 1
set @pivotRow = ''

create table #colsPivot
(
  id int IDENTITY(1,1),
  name varchar(20),
  CustId int
)

insert into #colsPivot
select 'Observacion', IDObservacionCustomer
from yourtable

set @pivotCount= (select COUNT(*) from #colsPivot) 

-- reset rowcount
set @rowCount = 1
set @query = ''

---- create the CASE string
while @rowCount <= @pivotCount
    begin
        set @pivotRow = (select Top 1 CustId from #colsPivot)

        set @query = @query + ', max(case when IDObservacionCustomer = ''' + @pivotRow + ''' then Observacion end) as ''Observacion_' + cast(@rowCount as varchar(10)) + ''''

        delete from #colsPivot where CustId = @pivotRow

        if @rowCount <= @pivotCount
            begin
                set @rowCount = @rowCount + 1
            end
    end

-- add the rest of the SQL Statement
set @query = 'SELECT IDProspecto ' + @query + ' from yourtable group by IDProspecto'

exec(@query)

See SQL Fiddle With Demo

Transpose a set of rows as columns in SQL Server 2000

The example at http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm only works if you know in advance what the row values can be. For example, let's say you have an entity with custom attributes and the custom attributes are implemented as rows in a child table, where the child table is basically variable/value pairs, and those variable/value pairs are configurable.

color red
size  big
city  Chicago

I'm going to describe a technique that works. I've used it. I'm NOT promoting it, but it works.

To pivot the data where you don't know what the values can be in advance, create a temp table on the fly with no columns. Then use a cursor to loop through your rows, issuing a dynamically built "alter table" for each variable, so that in the end your temp table has the columns, color, size, city.

Then you insert one row in your temp table, update it via another cursor through the variable, value pairs, and then select it, usually joined with its parent entity, in effect making it seem like those custom variable/value pairs were like built-in columns in the original parent entity.

Transformed rows to columns SQL Server 2000/2005

Take a look at the pivot feature, I think that would solve you're problem. For the SQL Server 2k you need to look into dynamic sql generation.

Rows in columns

Looks like this will work:

INSERT Table2
SELECT model.id, motor.id
FROM
    Table model,
    Table motor
WHERE
    model.Kind = 'MODEL'
    and motor.Kind = 'MOTOR'

SQL Query To Set Rows as Columns

I don't have a SQL Server 2000 instance handy to try, but see if this gets you anywhere. I'm a little surprised about the requirement to include columns conditionally - let's hope the application is built to handle the case where columns suddenly appear and disappear depending on the parameters and the changing data in the TBLFactorInspect table.

USE [tempdb];
GO
SET NOCOUNT ON;
GO

Setup:

CREATE TABLE dbo.TBLProduct
(
    ProductID INT PRIMARY KEY, ProductName NVARCHAR(50)
);

INSERT dbo.TBLProduct(ProductID, ProductName)
SELECT 1, 'Jacket'
UNION SELECT 2, 'Blazer'
UNION SELECT 3, 'Chaleco';

CREATE TABLE dbo.TBLFactors
(
    FactorID INT PRIMARY KEY, FactorName NVARCHAR(50)
);

INSERT dbo.TBLFactors(FactorID, FactorName)
SELECT 1, 'Length'
UNION SELECT 2, 'Threading'
UNION SELECT 3, 'Wool'
UNION SELECT 4, 'Cotton';

CREATE TABLE dbo.TBLFactorInspect
(
    ID INT PRIMARY KEY, ProductID INT,
    FactorID INT, FactorValue DECIMAL(5,2)
);

INSERT dbo.TBLFactorInspect(ID, ProductID, FactorID, FactorValue)
SELECT 1,1,1,5.00 UNION SELECT 2,1,2,5.55
UNION SELECT 3,2,2,6.33 UNION SELECT 4,2,3,3.66
UNION SELECT 5,2,4,1.05;
GO

Now some code:

CREATE PROCEDURE dbo.GetProductPivot
    @ProductID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(4000);
    SET @sql = N'';

    SELECT @sql = @sql + N'
        ' + QUOTENAME(FactorName) 
        + ' = MAX(CASE WHEN d.FactorID = ' 
        + RTRIM(FactorID) + ' THEN d.FactorValue END),'
    FROM
    (
        SELECT f.FactorID, f.FactorName
        FROM dbo.TBLFactorInspect AS d
        INNER JOIN dbo.TBLFactors AS f
        ON f.FactorID = d.FactorID
        WHERE (d.ProductID = @ProductID OR @ProductID IS NULL)
        GROUP BY f.FactorID, f.FactorName
    ) AS f
    ORDER BY f.FactorID;

    IF @sql = N''
    BEGIN
        SELECT ProductID, ProductName, 
            Result = 'No data in TBLFactorInspect'
            FROM dbo.TBLProduct
            WHERE ProductID = COALESCE(@ProductID, ProductID);
    END
    ELSE
    BEGIN
        SELECT @sql = N'SELECT p.ProductID, p.ProductName, ' + 
            LEFT(@sql, LEN(@sql)-1) + '
                FROM dbo.TBLProduct AS p
                LEFT OUTER JOIN dbo.TBLFactorInspect AS d
                ON p.ProductID = d.ProductID
                ' + CASE WHEN @ProductID IS NOT NULL THEN 
                ' WHERE p.ProductID = ' + RTRIM(@ProductID) ELSE '' END + '
                GROUP BY p.ProductID, p.ProductName
                ORDER BY p.ProductID;';

        EXEC sp_executeSQL @sql;
    END
END
GO

Some proof that it works with no parameters or specific productIDs:

EXEC dbo.GetProductPivot;
EXEC dbo.GetProductPivot @ProductID = 1;
EXEC dbo.GetProductPivot @ProductID = 2;
EXEC dbo.GetProductPivot @ProductID = 3;
GO

And now let's add a new factor and prove that it continues to work:

INSERT dbo.TBLFactors(FactorID, FactorName)
    SELECT 5, 'Tubing';

INSERT dbo.TBLFactorInspect(ID, ProductID, FactorID, FactorValue)
SELECT 6,1,5,2.75;
GO
EXEC dbo.GetProductPivot;
EXEC dbo.GetProductPivot @ProductID = 1;
GO

Clean up:

DROP TABLE dbo.TBLProduct, dbo.TBLFactors, dbo.TBLFactorInspect;
GO
DROP PROCEDURE dbo.GetProductPivot;
GO

SQL Query to convert columns to rows

SQL Server 2000, does not have an UNPIVOT function so you can use a UNION ALL to query this data and get it in the format that you want:

select date, hours
from
(
  select id, startdate date, sun hours
  from yourtable
  union all
  select id, dateadd(dd, 1, startdate), mon
  from yourtable
  union all
  select id, dateadd(dd, 2, startdate), tue
  from yourtable
  union all
  select id, dateadd(dd, 3, startdate), wed
  from yourtable
  union all
  select id, dateadd(dd, 4, startdate), thu
  from yourtable
  union all
  select id, dateadd(dd, 5, startdate), fri
  from yourtable
  union all
  select id, dateadd(dd, 6, startdate), sat
  from yourtable
) x
where date between '2012-10-15' and '2012-10-17'

See SQL Fiddle With Demo

SQL 2000 Row numbering with GROUP BY

Following script should get you started. In a nutshell, the script

• creates a temp table, adding an IDENTITY column to serve as rownumber
• inserts your original data into the temp table
• uses MIN(Rownumber) for each ID to get an offset.
• JOIN the temp table with the calculated offsets to restart the count for each group.

SQL Statement

CREATE TABLE #TempTable (Rownumber INTEGER IDENTITY(1, 1), ID INTEGER)
SET IDENTITY_INSERT #TempTable OFF

INSERT INTO #TempTable
SELECT      *
FROM        YourOriginalTable
ORDER BY    ID, int_Sort

SELECT    t.ID, t.Rownumber, t.Rownumber - o.Offset
FROM      #TempTable t
          INNER JOIN (
            SELECT    ID, MIN(Rownumber) - 1 AS Offset
            FROM      #TempTable
            GROUP BY  ID
          ) o ON o.ID = t.ID            

DROP TABLE #TempTable

Test script

DECLARE @YourTable TABLE (ID VARCHAR(1))
CREATE TABLE #TempTable (Rownumber INTEGER IDENTITY(1, 1), ID INTEGER)

SET IDENTITY_INSERT #TempTable OFF
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)

INSERT INTO #TempTable
SELECT      *
FROM        @YourTable
ORDER BY    ID

SELECT    t.ID, t.Rownumber, t.Rownumber - o.Offset
FROM      #TempTable t
          INNER JOIN (
            SELECT    ID, MIN(Rownumber) - 1 AS Offset
            FROM      #TempTable
            GROUP BY  ID
          ) o ON o.ID = t.ID            

DROP TABLE #TempTable

---

Related Topics