Rows to columns with a twist

Question

Rows to columns with a twist

Paul_J

Hall of Fame

Points: 3698
More actions
November 1, 2006 at 6:32 pm

#174829

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul_J Hall of Fame Points: 3698 More actions · Answer 2

Paul_J

Hall of Fame

Points: 3698

November 1, 2006 at 9:56 pm

#669385

Thanks!

Michael Meierruth SSChampion Points: 10051 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Eckhart SSCommitted Points: 1880 More actions · Answer 5

I was trying to understand what the code Jeff presented was doing and ran the following queries:

select null + ',' as addedtonull

select isnull(null,'yep') as justnull

select isnull(null + ',','yep') as addtonull

Why does addtonull come up as just "y" instead of "yep"?

Steve Eckhart

Ken McKelvey SSCoach Points: 18843 More actions · Answer 6

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

Michael Meierruth SSChampion Points: 10051 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)