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
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 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
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
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 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy