|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trasform columns to rowsHy
i have this table with one row: | col1 | col2 | col3 | col4 | col 5 | i must have a table with one row for each column:--------------------------------------- | txt1 | txt2 | txt3 | txt4 | txt5 | | col | Is there any way to do it with a sql query?------- txt1 txt2 txt3 txt4 txt5 Using UNION:
SELECT col1 AS col UNION SELECT col2 UNION SELECT col3 UNION SELECT col4 UNION SELECT col5 ; However, that result loses the information about which column the data came from. If that is significant then you'll probably want to modify the above: SELECT 1 AS col_no, col1 AS col UNION SELECT 2,col2 UNION SELECT 3,col3 UNION SELECT 4,col4 UNION SELECT 5,col5 ; -- David Portas SQL Server MVP -- Thanks you for your response...
i have try with UNION but i have too much columns and UNION do not run... for INSERT there is too much code to write... Do you have any other solution for my problem? Thanks > for INSERT there is too much code to write... Blame the table designer for this. :) Write one INSERT...SELECT statement, then use your favourite text editor to create the rest of the statements (clue: find and replace). ML In Query Analyzer, drag the column list from the Object Browser into the
editing window, then search and replace the commas with the UNION part of the query Run the UNION as an INSERT. If the server chokes on 1500 UNIONs then break it up into smaller INSERTs. -- Show quoteDavid Portas SQL Server MVP -- "Nicola" wrote: > Thanks you for your response... > i have try with UNION but i have too much columns and UNION > do not run... > for INSERT there is too much code to write... > Do you have any other solution for my problem? > > Thanks OK is a good idea with drag and replace
but with 1500 UNION i have 1500 SELECT and then 1500 scan to my table? is correct? isn't too much? sorry for my stubbornness... thank you for your attention Show quote "David Portas" wrote: > In Query Analyzer, drag the column list from the Object Browser into the > editing window, then search and replace the commas with the UNION part of the > query Run the UNION as an INSERT. If the server chokes on 1500 UNIONs then > break it up into smaller INSERTs. > > -- > David Portas > SQL Server MVP > -- > > > > "Nicola" wrote: > > > Thanks you for your response... > > i have try with UNION but i have too much columns and UNION > > do not run... > > for INSERT there is too much code to write... > > Do you have any other solution for my problem? > > > > Thanks Since you have so many columns (and obviously 1500 is an exaggeration;
max is 1024), you may want to script out an INSERT routine (using a cursor). Obviously, this breaks a lot of the best practice suggestions (don't use system tables, don't use cursors, and don't use dynamic SQL), but I'm hoping that this is NOT intended for production code. DECLARE @name sysname DECLARE @colID smallint DECLARE C CURSOR LOCAL FOR SELECT name, colid FROM syscolumns WHERE ID = OBJECT_ID('TableName') ORDER BY colid DECLARE @SQL nvarchar(4000) CREATE TABLE #tmp (name sysname, colID smallint, value varchar(7000)) OPEN C FETCH NEXT FROM C INTO @name, @colID WHILE @@Fetch_STATUS = 0 BEGIN SET @SQL = 'SELECT TOP 1 name=''' + @name + ''', colID=''' + CONVERT(varchar(6), @colid) + ''', value=' + @name + ' FROM TableName' INSERT INTO #tmp exec sp_executeSQL @SQL FETCH NEXT FROM C INTO @name, @colID END CLOSE C DEALLOCATE C SELECT * FROM #tmp DROP TABLE #tmp HTH, Stu insert into target
select field1 from source -- Show quoteinsert into target select field2 from source And so on till the end. "Nicola" wrote: > Hy > i have this table with one row: > > | col1 | col2 | col3 | col4 | col 5 | > --------------------------------------- > | txt1 | txt2 | txt3 | txt4 | txt5 | > > i must have a table with one row for each column: > > | col | > ------- > txt1 > txt2 > txt3 > txt4 > txt5 > > Is there any way to do it with a sql query? I guess cross tab reports is what you are looking for!
Cross Tab Report: Representing columns as Rows and Rows as Columns is known as cross tab report or PivotTable. Sample Table Structure and Data Create table TestPivot ( YearOfSales Int, SalesQuarter Int, Amount money ) go Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 1, 100) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 2, 200) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 3, 300) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 4, 400) go Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 1, 500) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 2, 600) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 3, 700) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 4, 800) go Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 1, 900) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 2, 1000) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 3, 1100) Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 4, 1200) go The below query is to create Cross tab report OR PIVOT table in SQL 2000. As expected it would work in SQL 2005 also. Select YearOfSales, sum(case SalesQuarter when 1 then Amount else 0 end) as Q1, sum(case SalesQuarter when 2 then Amount else 0 end) as Q2, sum(case SalesQuarter when 3 then Amount else 0 end) as Q3, sum(case SalesQuarter when 4 then Amount else 0 end) as Q4 from TestPivot group by YearOfSalesgo The below query is to create Cross tab report OR PIVOT table in SQL 2005 Select * from TestPivotPIVOT (sum(Amount) for SalesQuarter IN ([1], [2], [3], [4])) as Pt By any chance did this help you? :) Best Regards Vadivel Blog URL: http://vadivel.blogspot.com SQL Articles: http://vadivel.blogspot.com/2005/11/list-of-my-sql-articles-tips.html Show quote "Nicola" wrote: > Hy > i have this table with one row: > > | col1 | col2 | col3 | col4 | col 5 | > --------------------------------------- > | txt1 | txt2 | txt3 | txt4 | txt5 | > > i must have a table with one row for each column: > > | col | > ------- > txt1 > txt2 > txt3 > txt4 > txt5 > > Is there any way to do it with a sql query? Thanks for your response...
but i have 1 row and 1500 columns i can't build : "Select YearOfSales, sum(case SalesQuarter when 1 then Amount else 0 end) as Q1, sum(case SalesQuarter when 2 " ?????????? i don't have row number 2 :-( do you have any other solution? Show quote "Vadivel" wrote: > I guess cross tab reports is what you are looking for! > > Cross Tab Report: Representing columns as Rows and Rows as Columns is known > as cross tab report or PivotTable. > > Sample Table Structure and Data > > Create table TestPivot > ( > YearOfSales Int, > SalesQuarter Int, > Amount money > ) > go > > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 1, > 100) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 2, > 200) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 3, > 300) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2003, 4, > 400) > go > > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 1, > 500) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 2, > 600) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 3, > 700) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2004, 4, > 800) > go > > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 1, > 900) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 2, > 1000) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 3, > 1100) > Insert into TestPivot (YearOfSales, SalesQuarter, Amount) values (2005, 4, > 1200) > go > > The below query is to create Cross tab report OR PIVOT table in SQL 2000. As > expected it would work in SQL 2005 also. > > Select YearOfSales, > sum(case SalesQuarter when 1 then Amount else 0 end) as Q1, > sum(case SalesQuarter when 2 then Amount else 0 end) as Q2, > sum(case SalesQuarter when 3 then Amount else 0 end) as Q3, > sum(case SalesQuarter when 4 then Amount else 0 end) as Q4 > from TestPivot > group by YearOfSalesgo > > The below query is to create Cross tab report OR PIVOT table in SQL 2005 > > Select * from TestPivotPIVOT (sum(Amount) for SalesQuarter IN ([1], [2], > [3], [4])) as Pt > > By any chance did this help you? :) > > Best Regards > Vadivel > > Blog URL: http://vadivel.blogspot.com > SQL Articles: > http://vadivel.blogspot.com/2005/11/list-of-my-sql-articles-tips.html > > "Nicola" wrote: > > > Hy > > i have this table with one row: > > > > | col1 | col2 | col3 | col4 | col 5 | > > --------------------------------------- > > | txt1 | txt2 | txt3 | txt4 | txt5 | > > > > i must have a table with one row for each column: > > > > | col | > > ------- > > txt1 > > txt2 > > txt3 > > txt4 > > txt5 > > > > Is there any way to do it with a sql query? Nicola skrev:
> Thanks for your response... Sorry to jump in, but this sounds like some sort of record... 1500> but i have 1 row and 1500 columns > i can't build : > columns? Good you only have one row ;) /impslayer, aka Birger Johansson Sorry for my english...
yes i have 1500 columns... every columns is a special point of a image... i have one row after one select... i must write every point in a row of a text file and send this text file to my hardware: i have this table with one row: | col1 | col2 | col3 | col4 | col 5 |....| col1500 | i must have a table with one row for each column:--------------------------------------- | txt1 | txt2 | txt3 | txt4 | txt5 |.....| txt1500 | Show quote | col | ------- txt1 txt2 txt3 txt4 txt5 ..... txt1500 "impslayer" wrote: > > Nicola skrev: > > > Thanks for your response... > > but i have 1 row and 1500 columns > > i can't build : > > > > Sorry to jump in, but this sounds like some sort of record... 1500 > columns? > > Good you only have one row ;) > > /impslayer, aka Birger Johansson > > select 'UNION ALL select ' + quotename(column_name) + ' as Value from ' +
schema_name + '.' + table_name+ char(13) + char(10) from information_schema.columns See result pane. Cut and paste into another window. Adjust script generator for proper syntax. I home this helps. This is a good way to get statements without typing them (I hate typing). Show quote "Nicola" <Nic***@discussions.microsoft.com> wrote in message news:4436BB6D-9A3E-4715-AB8C-D69DD57B012D@microsoft.com... > Sorry for my english... > yes i have 1500 columns... > every columns is a special point of a image... > i have one row after one select... > i must write every point in a row of a text file > and send this text file to my hardware: > > > i have this table with one row: > > | col1 | col2 | col3 | col4 | col 5 |....| col1500 | > --------------------------------------- > | txt1 | txt2 | txt3 | txt4 | txt5 |.....| txt1500 | > > i must have a table with one row for each column: > > | col | > ------- > txt1 > txt2 > txt3 > txt4 > txt5 > .... > txt1500 > > > > "impslayer" wrote: > >> >> Nicola skrev: >> >> > Thanks for your response... >> > but i have 1 row and 1500 columns >> > i can't build : >> > >> >> Sorry to jump in, but this sounds like some sort of record... 1500 >> columns? >> >> Good you only have one row ;) >> >> /impslayer, aka Birger Johansson >> >> forgot
from information_schema.columns WHERE table_name='yourTableName' Show quote "Nicola" <Nic***@discussions.microsoft.com> wrote in message news:4436BB6D-9A3E-4715-AB8C-D69DD57B012D@microsoft.com... > Sorry for my english... > yes i have 1500 columns... > every columns is a special point of a image... > i have one row after one select... > i must write every point in a row of a text file > and send this text file to my hardware: > > > i have this table with one row: > > | col1 | col2 | col3 | col4 | col 5 |....| col1500 | > --------------------------------------- > | txt1 | txt2 | txt3 | txt4 | txt5 |.....| txt1500 | > > i must have a table with one row for each column: > > | col | > ------- > txt1 > txt2 > txt3 > txt4 > txt5 > .... > txt1500 > > > > "impslayer" wrote: > >> >> Nicola skrev: >> >> > Thanks for your response... >> > but i have 1 row and 1500 columns >> > i can't build : >> > >> >> Sorry to jump in, but this sounds like some sort of record... 1500 >> columns? >> >> Good you only have one row ;) >> >> /impslayer, aka Birger Johansson >> >> Are you using SQL Server for this? The maximum number of columns per
table is 1024. Stu Did my solution work for you? If not it would greatly help others to
help you if you could post enough information to reproduce the problem properly: DDL, sample INSERT and show your required end result (preferably you could cut the example down to just 5 or 10 columns rather than 1500). See the following explanation: http://www.aspfaq.com/etiquette.asp?id=5006 I admit that the meaning of your orginal spec actually seemed pretty obvious to me, even though you didn't post DDL. So obvious in fact that I don't understand why UNION wouldn't solve it for you. Maybe you missed my post in which case, see: http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_frm/thread/d8a5544152e4c067/3ad2fb3141ad3b57?lnk=st&rnum=3&hl=en#3ad2fb3141ad3b57 -- David Portas SQL Server MVP -- Nicola wrote:
Show quote > i have this table with one row: Use a helper table with integers and cross join to it:> > | col1 | col2 | col3 | col4 | col 5 | > --------------------------------------- > | txt1 | txt2 | txt3 | txt4 | txt5 | > > i must have a table with one row for each column: > > | col | > ------- > txt1 > txt2 > txt3 > txt4 > txt5 > > Is there any way to do it with a sql query? select case i when 1 then col1 when 2 then col2 when 3 then col3 when 4 then col4 ... when ?? then col?? end from my_table cross join (select i from num_table where i between 1 and ??) dt Dieter  |
|||||||||||||||||||||||
Other interesting topics