March 18, 2009 at 9:08 pm
that how the data is
SKU PO# QTY Date
1 '640050193' 'X1234' 122 '03/13/2009'
2 '640050193' 'V3344' 244 '02/11/2009'
3 '640050193' 'A1233' 43 '02/9/2009'
I want to convert it into columns this way
SKU PO#1 PO#2 Po#3 QTY1 QTY2 QTY3
1 '640050193' 'X1234' 'V3344' 'A1233' '122' '244' '43'
max number of PO I have is 3 and QTy is so there will 7 columns
March 19, 2009 at 12:51 am
Hi,
Slight misunderstand
You have the format like
slno,
pono,
sku,
qty,
date
And you need like this
slno,
sku,
pono,
qty
Is this correct?
ARUN SAS
March 19, 2009 at 9:41 am
Arun,
MY format is like this
SKU PO# QTY DATE
Abc w1 12 2009-03-12
Abc x2 22 2009-10-11
xyz r1 11 2009-01-01
xyz r2 33 2009-01-09
I want format to be like this
SKU PO1 PO2 QTY1 QTY2 DATE1 Date2
ABC w1 x2 12 22 2009-03-12 2009-10-11
xyz r1 r2 11 33 2009-01-01 2009-01-09
March 19, 2009 at 10:10 am
Is row order important to you?
Does it matter which row's values are stored in PO1, QTY1, Date1 and which in PO2, QTY2 and Date2, etc?
March 19, 2009 at 1:07 pm
no it doesn't matter which rows values are in PO1,QTY1 and in Date1 they can be random, but Date1 and QTY1 should be related to PO1.
so in the example above
when sku = 'abc'
PO1 could be W1 or X2
when SKU = xyz
PO1 could be r1 or r2
March 19, 2009 at 4:04 pm
Does this query do what you're looking for?
CREATE TABLE #xtabtesting (
SKU char(9) NOT NULL,
PO char(5) NOT NULL,
QTY int NOT NULL,
Date datetime NOT NULL
)
/* Test Data */
INSERT #xtabtesting (SKU, PO, QTY, Date)
SELECT '740050193', 'X1231', 234, '20090311' UNION ALL
SELECT '740050193', 'V3348', 120, '20090210' UNION ALL
SELECT '740050193', 'A1232', 145, '20090312' UNION ALL
SELECT '880050193', 'V1348', 125, '20080110' UNION ALL
SELECT '880050193', 'A6232', 162, '20080112'
SELECT X.SKU,
MAX(CASE WHEN RNK = 0 THEN X.PO END) AS PO1,
MAX(CASE WHEN RNK = 1 THEN X.PO END) AS PO2,
MAX(CASE WHEN RNK = 2 THEN X.PO END) AS PO3,
MAX(CASE WHEN RNK = 0 THEN X.QTY END) AS QTY1,
MAX(CASE WHEN RNK = 1 THEN X.QTY END) AS QTY2,
MAX(CASE WHEN RNK = 2 THEN X.QTY END) AS QTY3,
MAX(CASE WHEN RNK = 0 THEN X.Date END) AS Date1,
MAX(CASE WHEN RNK = 1 THEN X.Date END) AS Date2,
MAX(CASE WHEN RNK = 2 THEN X.Date END) AS Date3
FROM (
SELECT X1.SKU, X1.PO, X1.QTY, X1.Date,
COUNT(CASE WHEN X1.Date > X2.Date THEN 1 END) AS RNK
FROM #xtabtesting X1
INNER JOIN #xtabtesting X2 ON (X1.SKU = X2.SKU)
GROUP BY X1.SKU, X1.PO, X1.QTY, X1.Date
) X
GROUP BY X.SKU
The 3 sub-sets of column values (PO#, QTY#, Date#) will be ordered by the value of the Date# column. There will be a small performance hit in calculating the RNK column in the derived table, but it shouldn't be too bad, since there will only be a small number of rows for each distinct value of the SKU column.
March 20, 2009 at 4:59 pm
This was really close but when I ran this test data below My first row is coming up as all Null values
becuase ExpDate1 and Expdate 2 are same
SKU PO QTY ExpDate
''087-CH38'' ''P18611'' ''10'' ''2009-04-07''
''087-CH38''''P18642'' ''20'' ''2009-04-07''
''087-CH38''''P07201'' ''30'' ''2009-05-02''
March 20, 2009 at 5:12 pm
Is there a primary key on your source table?
If so, on which columns is it based?
If not, why not?
March 20, 2009 at 5:23 pm
there is no Primary key , table is populated by a text file. does setting up SKU columnas Primary key help
March 20, 2009 at 5:42 pm
does setting up SKU column as Primary key help
You can't use the SKU column as a primary key by itself as it's not unique.
If you were using SQL Server 2005, you could use the ROW_NUMBER function, but then with SQL Server 2005 you could also use the PIVOT clause.
In any case, every table ought to have a primary key. Can you automatically generate a primary key using an IDENTITY column when you import the data from the text file?
March 20, 2009 at 5:52 pm
well I have a linked Server which is 2005 , I can use that to extract data from 2000 add the rownum() and put the table back in 2000 with different table name. I have done this beofre for other tables, but will row number helps in this case ??
March 20, 2009 at 5:53 pm
well I have a linked Server which is 2005 , I can use that to extract data from 2000 add the rownum() and put the table back in 2000 with different table name. I have done this beofre for other tables, but will row number helps in this case ??
March 20, 2009 at 6:04 pm
In SQL Server 2005, the availability of the ROW_NUMBER function makes the query simple.
SELECT SKU,
MAX(CASE WHEN RNK = 1 THEN PO END) AS PO1,
MAX(CASE WHEN RNK = 2 THEN PO END) AS PO2,
MAX(CASE WHEN RNK = 3 THEN PO END) AS PO3,
MAX(CASE WHEN RNK = 1 THEN QTY END) AS QTY1,
MAX(CASE WHEN RNK = 2 THEN QTY END) AS QTY2,
MAX(CASE WHEN RNK = 3 THEN QTY END) AS QTY3,
MAX(CASE WHEN RNK = 1 THEN Date END) AS Date1,
MAX(CASE WHEN RNK = 2 THEN Date END) AS Date2,
MAX(CASE WHEN RNK = 3 THEN Date END) AS Date3
FROM (
SELECT SKU, PO, QTY, Date,
ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY Date) AS RNK
FROM #xtabtesting
) X
GROUP BY SKU
March 20, 2009 at 6:29 pm
Bingo,
well life is so much easier with SQL2005. I was trying to do it with cursor it was very cumbersome below is what I had but you beat me to this, the result in my case was all three Po's in one column but with ','
DECLARE @tbl TABLE (SKU varchar(55) PRIMARY KEY, PONumber VARCHAR(8000), QTY varchar(4000), ExpDate varchar(4000))
SET NOCOUNT ON
DECLARE @SKU varchar(55),@PONumber VARCHAR(55),@QTY varchar(55),@ExpDate varchar(55),
@SKUNext varchar(55),@PONumberNext VARCHAR(40),@QTYNext varchar(55),@ExpDatenext varchar(55)
DECLARE c CURSOR FOR
SELECT SKU, PONumber ,cast(QtyOutStanding as varchar(55)),convert(varchar,ExpectedDate,1)
FROM dbo.GSIrepPO
ORDER BY SKU, PONumber,QtyOutStanding,ExpectedDate;
OPEN c ;
FETCH NEXT FROM c INTO @SKUNext, @PONumberNext,@QTYNext,@ExpDatenext;
SET @SKU = @SKUNext;
WHILE @@FETCH_STATUS = 0 BEGIN
IF @SKUNext > @SKU
BEGIN
INSERT @tbl SELECT @SKU, @PONumber,@QTY ,@ExpDate ;
SELECT @PONumber = @PONumberNext, @SKU = @SKUNext,@QTY=@QTYNext,@ExpDate=@ExpDatenext ;
END ELSE
SET @PONumber = COALESCE(@PONumber + ';', SPACE(0)) + @PONumberNext ;
SET @QTY= COALESCE(@QTY + ';', SPACE(0)) + @QTYNext ;
SET @ExpDate = COALESCE(@ExpDate + ';', SPACE(0)) + @ExpDatenext ;
FETCH NEXT FROM c INTO @SKUNext, @PONumberNext,@QTYNext,@ExpDatenext
END
INSERT @tbl SELECT @SKU, @PONumber,@QTY ,@ExpDate ;
CLOSE c ;
DEALLOCATE c ;
SELECT * FROM @tbl ;
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply