November 1, 2006 at 6:32 pm
Hi all,
This is sort of a convert rows to columns problem, but it's a little more difficult and causing me major hassles.
I have a table with 2 columns and ANY number of rows (anywhere from 0 to 100).
Now, I want to get a table with a single row containing values of the second column of the original table, but I also want the values from the first column to now be the table column names.
So for example, if we start with the following table:
TABLE1 (Col1, Col2)
VALUES ('cat', 4)
VALUES ('dog', 2)
VALUES ('rat', 4)
VALUES ('hat', 1)
VALUES ('pet', 2)
VALUES ('cow', 5)
The result would be the following new table:
TABLE1 (cat, dog, rat, hat, pet, cow)
VALUES (4, 2, 4, 1, 2, 5)
Is this possible? Any help would be appreciated.
Cheers,
Paul
November 1, 2006 at 7:22 pm
I strongly suspect that your example data is missing something, but the following will do for what you have... lemme guess... spreadsheet output?
--=======================================================
-- This is just setting up for the test and is
-- NOT part of the solution.
--=======================================================
--===== Create a test table from the data given
CREATE TABLE Table1 (Col1 VARCHAR(10), Col2 INT)
INSERT INTO Table1(Col1,Col2)
SELECT 'cat', 4 UNION ALL
SELECT 'dog', 2 UNION ALL
SELECT 'rat', 4 UNION ALL
SELECT 'hat', 1 UNION ALL
SELECT 'pet', 2 UNION ALL
SELECT 'cow', 5
--=======================================================
-- Demo the solution
--=======================================================
--===== Declare some variables for dynamic SQL
DECLARE @SQLHead VARCHAR(8000)
DECLARE @SQLBody VARCHAR(8000)
DECLARE @SQLFoot VARCHAR(8000)
--===== Populate the variables using info from the table
SET @SQLHead = 'SELECT'
SELECT @SQLBody = ISNULL(@SQLBody+',','') + CHAR(13)
+ 'SUM(CASE WHEN Col1 = '''
+ Col1 + ''' THEN Col2 ELSE 0 END) AS '
+ Col1
FROM Table1
SELECT @SQLFoot = CHAR(13) + 'FROM Table1'
--===== Print the command we formed and execute it
PRINT @SQLHead+@SQLBody+@SQLFoot
EXEC (@SQLHead+@SQLBody+@SQLFoot)
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2006 at 9:56 pm
Thanks!
November 2, 2006 at 3:54 am
Jeff,
Even though this kind of dynamic sql leaves a lot of room for generating incomprehensible code, I'm interested in getting to know it better. Any place you recommend with loads of good examples?
Thanks.
November 2, 2006 at 6:06 am
Don't know of any coherent place dedicated to just doing crosstabs or just to dynamic SQL... there's a lot of really bad examples that use cursors, as well. But, if you search this forum for Crosstab, Dynamic, @SQL, and @MySQL, you'll find loads. Like I said, be careful... and consider carefully if you actually need to do something like this... some folks tend to overuse/abuse the technique. On the flip side, if you do need it, there's no substitute.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2006 at 11:09 am
I was trying to understand what the code Jeff presented was doing and ran the following queries:
select isnull(null,'yep') as justnull
select isnull(null + ',','yep') as addtonull
Why does addtonull come up as just "y" instead of "yep"?
November 3, 2006 at 11:40 am
November 3, 2006 at 1:49 pm
Go touch a null and you become a null. That's the rule of nulls.
Thus null +',' is equal to a null. That's why isnull(null + ',','yep') evaluates to 'yep'.
No matter where it hides it will show its presence in the form of a null.
Thus 1+(2+(3+(4+null))) equals null.
Of course, there are cases when nulls simply get ignored.
But sometimes nulls wreak havoc.
Sometimes nulls are a blessing.
Most of the time they are a pain in the buttock.
PS
Now that I've tried it, I'm stumped. It evaluates to 'y'! I can't believe it! So my part about nulls wreaking havoc is true!
But isnull(null + 'ab','yep') evaluates to 'ye'
and isnull(null + 'abc','yep') evalutates to 'yep'
Yep, there is problem.
November 3, 2006 at 5:04 pm
THAT, Ladies and Gentlemen, is why doing it this way is so important...
DECLARE @ANull VARCHAR(8000)
SET @ANull = NULL --was already null, just doing it here for effect
select @ANull + ',' as addedtonull
select isnull(@ANull,'yep') as justnull
select isnull(@ANull + ',','yep') as addtonull
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply