By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,521 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,521 IT Pros & Developers. It's quick & easy.

Converting Rows into Columns

P: 27
Hi,

I have written a query that I need to get data which are to be displayed in the SQL 2005 reports.

I am getting the result as



Data Count

A 2

B 4

C 5





I need to get the data in a single row and the data in the 'Data' column should become like a column, like



A B C

2 4 5



Can anyone help me with this query?
Mar 10 '08 #1
Share this Question
Share on Google+
8 Replies


ck9663
Expert 2.5K+
P: 2,878
Hi,

I have written a query that I need to get data which are to be displayed in the SQL 2005 reports.

I am getting the result as



Data Count

A 2

B 4

C 5





I need to get the data in a single row and the data in the 'Data' column should become like a column, like



A B C

2 4 5



Can anyone help me with this query?
You need to PIVOT the table. Check this and more here

-- CK
Mar 10 '08 #2

deepuv04
Expert 100+
P: 227
Hi,

I have written a query that I need to get data which are to be displayed in the SQL 2005 reports.

I am getting the result as



Data Count

A 2

B 4

C 5





I need to get the data in a single row and the data in the 'Data' column should become like a column, like



A B C

2 4 5



Can anyone help me with this query?
Hi,
To convert rows into columns you need to build dynamic sql
Try the following query..
Expand|Select|Wrap|Line Numbers
  1.     DECLARE @ReturnString varchar(1000)
  2.  
  3.     SET @ReturnString = 'SELECT '
  4.  
  5.     SELECT  @ReturnString = @ReturnString + convert(varchar(10),Count) +
  6.                                             ' AS '''+ Data +''', '
  7.     FROM    Table_Name
  8.  
  9.     SET @ReturnString = substring(@ReturnString,0,len(@ReturnString))
  10.     Print @ReturnString
  11.  
  12.     EXEC (@ReturnString)
  13.  
Mar 10 '08 #3

P: 27
I have the query this way, can u please help me again:

SELECT
dbo.Certification_Category_Master.Data,

COUNT(dbo.Certification.Certification_ID ) AS [COUNT]
FROM dbo.Certification INNER JOIN
dbo.Certification_Master ON dbo.Certification.Certification_ID = dbo.Certification_Master.Certification_ID INNER JOIN
dbo.EMPLOYEES ON dbo.Certification.Employee_Code = dbo.EMPLOYEES.EMPLID INNER JOIN
dbo.Certification_Category_Master ON
dbo.Certification_Master.Certification_Category_ID = dbo.Certification_Category_Master.Certification_Ca tegory_ID
WHERE (YEAR(dbo.Certification.Updated_On) = 2008) AND (dbo.Certification.Certification_Status_ID = '6') AND (MONTH(dbo.Certification.Updated_On) = 1)
AND dbo.EMPLOYEES.Discipline_Name LIKE '%'+ '' +'%'
GROUP BY dbo.Certification_Category_Master.Data
Mar 10 '08 #4

deepuv04
Expert 100+
P: 227
I have the query this way, can u please help me again:

SELECT
dbo.Certification_Category_Master.Data,

COUNT(dbo.Certification.Certification_ID ) AS [COUNT]
FROM dbo.Certification INNER JOIN
dbo.Certification_Master ON dbo.Certification.Certification_ID = dbo.Certification_Master.Certification_ID INNER JOIN
dbo.EMPLOYEES ON dbo.Certification.Employee_Code = dbo.EMPLOYEES.EMPLID INNER JOIN
dbo.Certification_Category_Master ON
dbo.Certification_Master.Certification_Category_ID = dbo.Certification_Category_Master.Certification_Ca tegory_ID
WHERE (YEAR(dbo.Certification.Updated_On) = 2008) AND (dbo.Certification.Certification_Status_ID = '6') AND (MONTH(dbo.Certification.Updated_On) = 1)
AND dbo.EMPLOYEES.Discipline_Name LIKE '%'+ '' +'%'
GROUP BY dbo.Certification_Category_Master.Data
Expand|Select|Wrap|Line Numbers
  1.     DECLARE @ReturnString varchar(1000)
  2.  
  3.     SET @ReturnString = 'SELECT '
  4.  
  5.     SELECT  @ReturnString = @ReturnString + 
  6.             CAST( COUNT(dbo.Certification.Certification_ID ) AS VARCHAR) AS  +
  7.                                             ' AS '''+ dbo.Certification_Category_Master.Data +''', '
  8.         FROM dbo.Certification INNER JOIN
  9.         dbo.Certification_Master ON dbo.Certification.Certification_ID = dbo.Certification_Master.Certification_ID INNER JOIN
  10.         dbo.EMPLOYEES ON dbo.Certification.Employee_Code = dbo.EMPLOYEES.EMPLID INNER JOIN
  11.         dbo.Certification_Category_Master ON
  12.         dbo.Certification_Master.Certification_Category_ID = dbo.Certification_Category_Master.Certification_Ca tegory_ID
  13.         WHERE (YEAR(dbo.Certification.Updated_On) = 2008) AND (dbo.Certification.Certification_Status_ID = '6') AND (MONTH(dbo.Certification.Updated_On) = 1)
  14.         AND dbo.EMPLOYEES.Discipline_Name LIKE '%'+ '' +'%'
  15.         GROUP BY dbo.Certification_Category_Master.Data
  16.  
  17.  
  18.     SET @ReturnString = substring(@ReturnString,0,len(@ReturnString))
  19.     Print @ReturnString
  20.  
  21.     EXEC (@ReturnString)
  22.  
  23.  
Mar 10 '08 #5

P: 2
please help me write a script:


ACC1 ACC2 VALUE1 VALUE2 111 222 333 444
111 222 10 0 111 0 10 0 0
222 111 0 10 ==> 222 10 0 0 0
333 444 10 0 333 0 0 0 10
444 333 0 10 444 0 0 10 0
Mar 13 '08 #6

P: 2
Sorry
Help Me Convert

A B C D
1 2 * 0
2 1 0 *
3 4 * 0
4 3 0 *

To

1 2 3 4
1 0 * 0 0
2 * 0 0 0
3 0 0 0 *
4 0 0 * 0
Mar 13 '08 #7

deepuv04
Expert 100+
P: 227
Sorry
Help Me Convert

A B C D
1 2 * 0
2 1 0 *
3 4 * 0
4 3 0 *

To

1 2 3 4
1 0 * 0 0
2 * 0 0 0
3 0 0 0 *
4 0 0 * 0
can you explain the above relation please...
Mar 13 '08 #8

deepuv04
Expert 100+
P: 227
can you explain the above relation please...
Hi,
here is the query you want.
Apply the proper table names and column names that suits your database

Expand|Select|Wrap|Line Numbers
  1. DECLARE @Sql varchar(max)
  2.  
  3. DECLARE @i int,@max int
  4.  
  5. SELECT @i = 1 ,@max = count(*) from rowCol
  6.  
  7. select @Sql = 'SELECT Acc1,'
  8.  
  9. while (@i <= @max)
  10. BEGIN
  11.     SELECT @Sql = @Sql +
  12.                  'isnull((SELECT Case x1.Value1 WHEN 0 then x1.Value2 ELSE x1.VALUE1 end 
  13.                           FROM rowcol t1 inner join
  14.                             ( SELECT ROW_NUMBER() OVER (ORDER BY ACC2) AS Rank,* 
  15.                               FROM RowCol ) AS X1 on x1.Acc1 = T1.Acc1
  16.                           WHERE Rank = ' + cast(@i as varchar) + ' and x1.Acc1 = T.Acc1 AND t1.Acc2 = x1.Acc2),0) as v' + cast(@i as varchar) + ','
  17.     set @i = @i + 1
  18. END
  19.  
  20.     SELECT @Sql = SUBSTRING(@Sql,0,len(@Sql))
  21.  
  22.     SELECT @Sql = @Sql + ' FROM RowCol as T'
  23. --PRINT @Sql
  24.  
  25. EXEC (@Sql)
  26.  
Mar 17 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.