--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
Post by Plamen RatchevSELECT 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
Post by Plamen RatchevSELECT 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
Post by Tony RogersonThis 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--
Post by Michael ColeAh, 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
Thanks Michael, but I am sure people will get my meaning entirely.
Tony.
Post by SydneyHello,
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
ProductID Descr Qty
--------------------------------------------
001 Mouse 10
002 Cable 20
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
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.
--
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/prodinfo/previousversions/books.mspx
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-Databases-Mana-Takahashi/dp/1593271905
All you are getting here is DANGEROUS kludges.
Post by SydneyPlamen,
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
Post by Plamen RatchevYou 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
Post by SydneyPlamen,
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