November 2, 2006 at 12:14 am
Hi frnds ,
I have a problem . Actually , I have to show the data of column in the form of rows .
The table will contain only one row . It will have 10 columns . And my problem is to display all the values of the column in one single column ..
I have one solution for it as we can select fields one by one and then merging then with the help of union all . But this is not the most effective way of doing it . If anybody have any better solution , plz. mail me at
Thanks
Ashish
November 2, 2006 at 2:07 am
hi
if u r using sql 2005 u can use unpivot operator for this.
"Keep Trying"
November 2, 2006 at 4:21 am
Use nested selects such as
select (select x from myTable where rowNum=1) as col1, (select x from myTable where rowNum=2) as col2, (select x from myTable where rowNum=3) as col3, (select x from myTable where rowNum=4) as col4, (select x from myTable where rowNum=5) as col5, .... (select x from myTable where rowNum=10) as col10
This is similar to what the pivoting operations in SQL 2005 can help you do but will work in SQL 7/2000. There are MANY articles on this site about pivoting data - if what I presented abve is not sophisticated enough then some of those articles will point you in the right direction. Certainly the above script doesn't help if your data is dynamic. Perhaps you could post what your thoughts on the matter were and we might understand some more about your data.
Cheers
November 3, 2006 at 3:46 am
Here is a generic function I wrote several years ago for the same purpose. I'm sure there are newer/better ways, but this has always helped me in a pinch. Feel free to use/adjust as needed.
--CREATE PROCEDURE ConvertRowToColumns
--(
DECLARE
@Table varchar(100),
@Cond varchar(3000)
--)
SELECT @Table = '',
@Cond = ''
--AS
DECLARE @CN varchar(100),
@tempTable1 varchar(100),
@tempTable2 varchar(100),
@SQL varchar(3000),
@val varchar(3000)
-- Create unique temp table name. Make it global using minutes and seconds so any process can access it if needed
SELECT @tempTable1 = '##MyTable' + CONVERT(varchar, DATEPART(mi, GETDATE())) + CONVERT(varchar, DATEPART(ss, GETDATE())) + CONVERT(varchar, DATEPART(ms, GETDATE())) SELECT @tempTable2 = @tempTable1 + 'a'
-- Create the second temp table
SELECT @SQL = 'CREATE TABLE ' + @tempTable2 + '
( TABLE_NAME varchar(100) NULL,
COLUMN_NAME varchar(100) PRIMARY KEY,
ORDINAL_POSITION varchar(100) NULL,
COLUMN_DEFAULT varchar(100) NULL,
IS_NULLABLE varchar(100) NULL,
DATA_TYPE varchar(100) NULL,
CHARACTER_MAXIMUM_LENGTH varchar(100) NULL,
IDENTITY_COL int NULL,
COL_VAL varchar(3000) NULL
)'
EXEC (@SQL)
-- Get the data
SELECT @SQL = 'SELECT *, IDENTITYCOL AS IDCOL INTO ' + @tempTable1 + ' FROM ' + @Table
IF LEN(@Cond) > 0 SET @SQL = @SQL + ' WHERE ' + @Cond
EXEC (@SQL)
-- Fill in the base data from the column information schema
SELECT @SQL = 'INSERT INTO ' + @tempTable2 + '(
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
)
SELECT
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = ' + CHAR(39) + @Table + CHAR(39) + ')
'
EXEC (@SQL)
-- Get the column information
DECLARE Cols CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @Table)
ORDER BY ORDINAL_POSITION
OPEN Cols
FETCH NEXT FROM Cols INTO @CN
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
-- For each column name, retrieve the information from the related column in temp1 and store in temp2
SELECT @SQL = 'UPDATE ' + @tempTable2 + '
SET IDENTITY_COL = T1.IDCOL,
COL_VAL = T1.' + @CN + '
FROM (SELECT TOP 1 ' + @CN + ', IDCOL FROM ' + @tempTable1 + ') T1
WHERE COLUMN_NAME = ' + CHAR(39) + @CN + CHAR(39) + ' '
EXEC (@SQL)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
FETCH NEXT FROM Cols INTO @CN
END
CLOSE Cols
DEALLOCATE Cols
-- Select all records from temp2
SELECT @SQL = 'SELECT '
-- + ' TABLE_NAME, '
+ ' COLUMN_NAME, '
-- + ' ORDINAL_POSITION, '
-- + ' COLUMN_DEFAULT, '
-- + ' IS_NULLABLE, '
-- + ' DATA_TYPE, '
-- + ' CHARACTER_MAXIMUM_LENGTH,'
-- + ' IDENTITY_COL,'
+ ' COL_VAL
FROM ' + @tempTable2 + ' ORDER BY CONVERT(int, ORDINAL_POSITION)' EXEC (@SQL)
-- Drop temp tables
SELECT @SQL = 'DROP TABLE ' + @tempTable1 + '
DROP TABLE ' + @tempTable2
EXEC (@SQL)
GO
November 3, 2006 at 7:07 am
The faster way to convert columns into rows is this make a cross join with a generic table that have one columns and as much rows as column you have in the table on this case 10.
something like this
select CASE WHEN COLNUM = 1 THEN mytable.col1
WHEN COLNUM = 2 THEN mytable.col2
....
WHEN COLNUM = 10 THEN mytable.col10
end
from mytable
CROSS JOIN (SELECT 1 as COlNUM UNION SELECT 2 as COLNUM UNION ... SELECT 10 as COLNUM )
so far this is the faster way that i have found.
November 6, 2006 at 11:56 pm
the above method sounds trueif and only if the developer knows what is the exact row value in the 10 rows that he wish to convert as columns....
what if there are more than 10 rows that are fetched and then needs to be converted as columns and wat if the developer does not know what are the values that he is going to fetch...the more appropriate way to do this is to generate a dynamic create table query with recordset thus generated....
something like:
select identity(int, 1, 1) as slno, ColId , Column_Name Into #rows2cols
from MyTable -- from which the rows to columns data will come
declare @cnt int
declare @STR varchar(2000)
select @cnt = count(*) from #rows2cols
select @STR= 'create table #Table (SNo varchar(50),Name varchar(50),'
while @cnt > 0
Begin
set @STR = @STR + ( select '[' + Column_Name + ']' from #rows2cols where slno = @cnt ) + ' varchar(50),'
SET @cnt = @cnt - 1
End
set @STR = substring(@str, len(@str) -1) + ')' -- substring is used to exclude the last "," after the loop exits
exec (@str)
NOW this will create table query as a sql statement from a while loop till the last row value is hit.
use and modify...
Let me know if it works...
Nitin'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply