May 4, 2010 at 8:29 pm
Gianluca Sartori (5/4/2010)
Nice code, Mr. Coffee!Looks like you specialized in pivot/unpivot lately...
Oh am flattened! :blush: Thanks, Mr.Tally from Italy!!
Of late this PIVOT and UNPIVOT requirements are coming in large here at SSC.To be really honest, i learned how to pivot/unpivot only from you guys. So i must credit my knowledge only to you people. And am just a junior developer, so as a famous line goes , miles to go before i sleep!!
Thanks, dear friend!
May 5, 2010 at 7:21 am
hm - interesting idea, let me see if it will work for my application... will post back shortly
May 5, 2010 at 7:40 am
Alrighty - I've come up with a solution that I think will be acceptable for my purposes. One thing that i should have noted is that i'll only be dealing with one row frpm Table 1 at a time, because of the way the functionality will be working. So, what i'll do is, take variables for each field, and then just update the variables through the join:
DECLARE @Field1 INT
DECLARE @Field2 INT
DECLARE @Field3 INT
DECLARE @Field4 INT
SELECT @Field1 = Field1, @Field2 = Field2, @Field3 = Field3, @Field4 = Field4
FROM @test-2
SELECT
@Field1 = (CASE WHEN @Field1 = 0 THEN t2.Field1 ELSE @Field1 END),
@Field2 = (CASE WHEN @Field2 = 0 THEN t2.Field2 ELSE @Field2 END),
@Field3 = (CASE WHEN @Field3 = 0 THEN t2.Field3 ELSE @Field3 END),
@Field4 = (CASE WHEN @Field4 = 0 THEN t2.Field4 ELSE @Field4 END)
FROM @test-2 t1
JOIN @Test2 t2 ON t1.ID = t2.T1ID
SELECT @Field1, @Field2, @Field3, @Field4
This produces the output for the row in question
May 5, 2010 at 7:55 am
kramaswamy (5/5/2010)
DECLARE @Field1 INT
DECLARE @Field2 INT
DECLARE @Field3 INT
DECLARE @Field4 INT
SELECT @Field1 = Field1, @Field2 = Field2, @Field3 = Field3, @Field4 = Field4
FROM @test-2
Wont this populate the local variables with the last row in the table? So, you will be writing a cursor/while loop code that will fetch Row-By-Row values? Hmmm... Interesting..
May 5, 2010 at 8:04 am
Sorry - I should have elaborated on that one too, lol. My bad.
Rather, as a full solution, and using the above sample data,
DECLARE @NewID INT
DECLARE @OldID INT
SET @NewID = 0
SET @OldID = 0
DECLARE @Field1 INT
DECLARE @Field2 INT
DECLARE @Field3 INT
DECLARE @Field4 INT
WHILE 1 = 1
BEGIN
SET @Field1 = 0
SET @Field2 = 0
SET @Field3 = 0
SET @Field4 = 0
SELECT TOP 1
@NewID = ID,
@Field1 = Field1,
@Field2 = Field2,
@Field3 = Field3,
@Field4 = Field4
FROM @test-2
WHERE ID > @NewID
ORDER BY ID
IF @NewID = @OldID BREAK
SELECT
@Field1 = (CASE WHEN @Field1 = 0 THEN Field1 ELSE @Field1 END),
@Field2 = (CASE WHEN @Field2 = 0 THEN Field2 ELSE @Field2 END),
@Field3 = (CASE WHEN @Field3 = 0 THEN Field3 ELSE @Field3 END),
@Field4 = (CASE WHEN @Field4 = 0 THEN Field4 ELSE @Field4 END)
FROM @Test2
WHERE T1ID = @NewID
ORDER BY ID
PRINT @Field1
PRINT @Field2
PRINT @Field3
PRINT @Field4
SET @OldID = @NewID
END
May 5, 2010 at 8:13 am
Your technique for retrieving the first non-zero value in variables is quite smart, but please be advised that the order is NOT guaranteed. It may work today, could go totally quirks tomorrow.
I would consider using ColdCoffee's solution.
-- Gianluca Sartori
May 5, 2010 at 8:37 am
How is order not guaranteed? I'm ordering it by ID, which is the primary key - the order should be absolute, no?
May 5, 2010 at 10:12 am
No, unfortunately order is not guaranteed when assigning to variables.
I couldn't believe it when I first heard of it, but I can confirm it is so.
-- Gianluca Sartori
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply