Converting Rows into Columns

[kbipul]

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?

[ck9663]

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

[deepuv04]

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..
[code=sql]
DECLARE @ReturnString varchar(1000)

SET @ReturnString = 'SELECT '

SELECT @ReturnString = @ReturnString + convert(varchar (10),Count) +
' AS '''+ Data +''', '
FROM Table_Name

SET @ReturnString = substring(@Retu rnString,0,len( @ReturnString))
Print @ReturnString

EXEC (@ReturnString)
[/code]

[kbipul]

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

SELECT
dbo.Certificati on_Category_Mas ter.Data,

COUNT(dbo.Certi fication.Certif ication_ID ) AS [COUNT]
FROM dbo.Certificati on INNER JOIN
dbo.Certificati on_Master ON dbo.Certificati on.Certificatio n_ID = dbo.Certificati on_Master.Certi fication_ID INNER JOIN
dbo.EMPLOYEES ON dbo.Certificati on.Employee_Cod e = dbo.EMPLOYEES.E MPLID INNER JOIN
dbo.Certificati on_Category_Mas ter ON
dbo.Certificati on_Master.Certi fication_Catego ry_ID = dbo.Certificati on_Category_Mas ter.Certificati on_Category_ID
WHERE (YEAR(dbo.Certi fication.Update d_On) = 2008) AND (dbo.Certificat ion.Certificati on_Status_ID = '6') AND (MONTH(dbo.Cert ification.Updat ed_On) = 1)
AND dbo.EMPLOYEES.D iscipline_Name LIKE '%'+ '' +'%'
GROUP BY dbo.Certificati on_Category_Mas ter.Data

[deepuv04]

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

SELECT
dbo.Certificati on_Category_Mas ter.Data,

COUNT(dbo.Certi fication.Certif ication_ID ) AS [COUNT]
FROM dbo.Certificati on INNER JOIN
dbo.Certificati on_Master ON dbo.Certificati on.Certificatio n_ID = dbo.Certificati on_Master.Certi fication_ID INNER JOIN
dbo.EMPLOYEES ON dbo.Certificati on.Employee_Cod e = dbo.EMPLOYEES.E MPLID INNER JOIN
dbo.Certificati on_Category_Mas ter ON
dbo.Certificati on_Master.Certi fication_Catego ry_ID = dbo.Certificati on_Category_Mas ter.Certificati on_Category_ID
WHERE (YEAR(dbo.Certi fication.Update d_On) = 2008) AND (dbo.Certificat ion.Certificati on_Status_ID = '6') AND (MONTH(dbo.Cert ification.Updat ed_On) = 1)
AND dbo.EMPLOYEES.D iscipline_Name LIKE '%'+ '' +'%'
GROUP BY dbo.Certificati on_Category_Mas ter.Data

[code=sql]
DECLARE @ReturnString varchar(1000)

SET @ReturnString = 'SELECT '

SELECT @ReturnString = @ReturnString +
CAST( COUNT(dbo.Certi fication.Certif ication_ID ) AS VARCHAR) AS +
' AS '''+ dbo.Certificati on_Category_Mas ter.Data +''', '
FROM dbo.Certificati on INNER JOIN
dbo.Certificati on_Master ON dbo.Certificati on.Certificatio n_ID = dbo.Certificati on_Master.Certi fication_ID INNER JOIN
dbo.EMPLOYEES ON dbo.Certificati on.Employee_Cod e = dbo.EMPLOYEES.E MPLID INNER JOIN
dbo.Certificati on_Category_Mas ter ON
dbo.Certificati on_Master.Certi fication_Catego ry_ID = dbo.Certificati on_Category_Mas ter.Certificati on_Ca tegory_ID
WHERE (YEAR(dbo.Certi fication.Update d_On) = 2008) AND (dbo.Certificat ion.Certificati on_Status_ID = '6') AND (MONTH(dbo.Cert ification.Updat ed_On) = 1)
AND dbo.EMPLOYEES.D iscipline_Name LIKE '%'+ '' +'%'
GROUP BY dbo.Certificati on_Category_Mas ter.Data


SET @ReturnString = substring(@Retu rnString,0,len( @ReturnString))
Print @ReturnString

EXEC (@ReturnString)

[/code]

[tuanlvns]

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

[tuanlvns]

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

[deepuv04]

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

[deepuv04]

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

[Code=Sql]
DECLARE @Sql varchar(max)

DECLARE @i int,@max int

SELECT @i = 1 ,@max = count(*) from rowCol

select @Sql = 'SELECT Acc1,'

while (@i <= @max)
BEGIN
SELECT @Sql = @Sql +
'isnull((SELECT Case x1.Value1 WHEN 0 then x1.Value2 ELSE x1.VALUE1 end
FROM rowcol t1 inner join
( SELECT ROW_NUMBER() OVER (ORDER BY ACC2) AS Rank,*
FROM RowCol ) AS X1 on x1.Acc1 = T1.Acc1
WHERE Rank = ' + cast(@i as varchar) + ' and x1.Acc1 = T.Acc1 AND t1.Acc2 = x1.Acc2),0) as v' + cast(@i as varchar) + ','
set @i = @i + 1
END

SELECT @Sql = SUBSTRING(@Sql, 0,len(@Sql))

SELECT @Sql = @Sql + ' FROM RowCol as T'
--PRINT @Sql

EXEC (@Sql)
[/code]