Converting columns to rows NEED HELP

#11

You use a cross join with table with two rows (in this case it was a simple derived table with two numbers in each row)
to produce twice the number of rows in the original table (since the cross join will generate the combination of all
possible row matches). Then the CASE expression is used to pick the correct value corresponding to the row you want. In
this case for the first row it picks descriptions and for the second quantity.

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

#12

--we can write generic solution for any query.

--sample table

SELECT * INTO #comp
FROM ( SELECT '001' ProductID, 'Mouse' Descr, 10 Qty
UNION all
SELECT '002' ProductID, 'Cable' Descr, 20 Qty
) x

--selected record to #tmp
SELECT * INTO #tmp FROM #comp -- WHERE car_id=1

--generic solution
DECLARE @sql VARCHAR(max)
SET @sql=''
SELECT @sql=@sql + 'SELECT ProductID,'''+Name+''' as ColumnName,cast(['+Name+'] as varchar(500)) as Value FROM #tmp union all '
FROM tempdb.sys.columns s
where object_id=object_id('tempdb..#tmp') AND s.NAME IN ('Descr','Qty')

SET @sql = LEFT(@sql,LEN(@sql)-9)

SET @sql = 'select distinct ProductID,Value from (' + @sql + ') x'

EXEC( @sql)

DROP TABLE #tmp,#comp

Quote:

On Thursday, October 08, 2009 6:24 PM Plamen Ratchev wrote:

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

Quote:

On Thursday, October 08, 2009 10:21 PM Plamen Ratchev wrote:

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

Quote:

On Friday, October 09, 2009 2:24 AM Tony Rogerson wrote:

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--

Quote:

On Friday, October 09, 2009 6:17 AM Michael Cole 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 will not perpetuate myths...

--
Regards

Michael Cole

Quote:

On Friday, October 09, 2009 7:33 AM Tony Rogerson wrote:

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

Tony.

Quote:

On Sunday, October 11, 2009 9:07 AM Sydney wrote:

Hello,

I would like to know if it is 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 is 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

Quote:

On Sunday, October 11, 2009 9:07 AM Erland Sommarskog wrote:

Sydney (sydney.luu (AT) gmail (DOT) com) writes:

If you are on SQL 2005, look up UNPIVOT in Books Online. And, no, I am
not going to show you an example; I am 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

Quote:

On Sunday, October 11, 2009 9:07 AM --CELKO-- wrote:

ill convert columns of information into rows of information. =A0

This is best done with a report writer.

ProductID =A0 =A0 =A0 =A0 =A0Item
-----------------------------------
001 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Mouse
001 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
002 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cable
002 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 20
-----------------------------------

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

If you do not 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.

Quote:

On Sunday, October 11, 2009 9:07 AM Sydney wrote:

pe/size as 'descr'):

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

Quote:

On Monday, October 12, 2009 2:36 PM Plamen Ratchev wrote:

You use a cross join with table with two rows (in this case it was a simple derived table with two numbers in each row)
to produce twice the number of rows in the original table (since the cross join will generate the combination of all
possible row matches). Then the CASE expression is used to pick the correct value corresponding to the row you want. In
this case for the first row it picks descriptions and for the second quantity.

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

Quote:

On Monday, October 12, 2009 9:35 PM Sydney 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 is 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