Hello,

I would like to know if it's possible to compose a TSQL statement that
will convert columns of information into rows of information. For
example:

I have the following table of info:

ProductID Descr Qty
--------------------------------------------
001 Mouse 10
002 Cable 20

and want the result to look like this:

ProductID Item
-----------------------------------
001 Mouse
001 10
002 Cable
002 20
-----------------------------------

I know it's strange to have this type of output but I have a need to
do something like this.
in TSQL. Please show how this is accomplished.

Any help I can get from this group is greatly appreciated.


-Sydney

Sydney (sydney.luu (AT) gmail (DOT) com) writes:
If you are on SQL 2005, look up UNPIVOT in Books Online. And, no, I'm
not going to show you an example; I'm just about to sign off for tonight.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

This should do it (make sure to cast the 'qty' column to the same data type/size as 'descr'):

SELECT productid, value
FROM (SELECT productid, descr, CAST(qty AS VARCHAR(20)) AS qty
FROM Info) AS I
UNPIVOT
(value FOR col IN (descr, qty)) AS U;

--
Plamen Ratchev
http://www.SQLStudio.com

This is best done with a report writer.

-----------------------------------
001 * * * * * * * * * Mouse
001 * * * * * * * * * 10
002 * * * * * * * * * Cable
002 * * * * * * * * * 20
-----------------------------------
<<

Did you notice that the vague "item_<nothing in particular>" has data
of two different types and meanings??

If you don't know anything about RDBMS, then get a copy of the
simplest intro book I know --
http://www.amazon.com/Manga-Guide-Da.../dp/1593271905

All you are getting here is DANGEROUS kludges.

On Oct 8, 6:24*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:

Plamen,

How would you do it in SQL2K? Sorry I forgot to mention in my
original post.


Celko,

Yes, I did realize the "Item" column in my sample contains two
different data types. It was only an example
not a real world data.

Thanks



-Sydney

Here is SQL Server 2000 version:

SELECT productid,
CASE n WHEN 1 THEN descr
WHEN 2 THEN qty
END AS value
FROM (SELECT productid, descr, CAST(qty AS VARCHAR(20)) AS qty
FROM Info) AS I
CROSS JOIN (SELECT 1 UNION SELECT 2) AS Nums(n);

--
Plamen Ratchev
http://www.SQLStudio.com

This is down to your lack of real industrial experience.

Its akin to the group of people who persisted that the Earth was flat - they
swore blind it was flat (aka you and your kludge and normal form rants);
unfortunate for them, there where people who had open minds and actually
found the Earth was round - it was those folk that made it to the new lands
(America).

You owe your life to open minded people!

Just in the same way posters on here are thankful there are open minded
people on here that give them workable solutions to their real business
problems.

--ROGGIE--

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

Tony Rogerson <tonyrogerson (AT) torver (DOT) net> wrote


Ah, minor point here. AFAIAA, no Greek of around 300BCE ever made it to
America.

(In other words, the idea that Columbus proved the Earth was round is a
myth - it was known that it was a spheroid over 2000 years before his
journey. Look up Aristotle and Erastosthenes.)

And hopefully such open-minded people won't perpetuate myths...

--
Regards

Michael Cole

Thanks Michael, but I'm sure people will get my meaning entirely.

Tony.

"Michael Cole" <none (AT) invalid (DOT) com> wrote

On Oct 8, 10:21*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
Plamen,

Thank you for your sample!

I have modified your code to work with what I needed to do in a real
database table. It's working
beautifully. I tried to study your TSQL and it looks like trick is
the CROSS JOIN and the UNION.
I do some have experience in TSQL but not at a level I want to be in
yet. Can you explain how the
"n" (as in CASE n ....) and how Nums(n) work? I am going to do some
research as well.

Thanks.

Sydney