How to convert multiple row into single row and multiple column in by sql querry...??

Question

Hello Guys,

I have a Table Like this,

Month_Name   |   Value

JAN                  |      Y

FEB                  |      N

MAR                 |     N

APR                  |     Y

MAY                  |     N

JUN                  |      N

And now I wanted to convert a new table using SQL SERVER 2005 which will look something like

this

FIRST             |      SECOND

JAN                |       APR

Means those value column field is 'Y'will show only.

Answer 1

check this out. 

MonthName ValueName
Jan Y
Feb N
Mar N
Apr Y
May  N
Jun N

select Jan as First, Apr as Second from Demo
pivot (max (monthname) for monthname in ([Jan],[Feb],[Mar],[Apr],[May])) as Months
where ValueName = 'Y'

Output

First Second
Jan Apr

Thanks,

---

Parth

Answer 2

Hello,

This thread is not related to SQL Server Data Access but to T-SQL statements. It is why i will move it to the TRANSACT-SQL Forum where it will interest more people.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Hi,

DECLARE @data TABLE
(
	Month_Name VARCHAR(10),
	Value CHAR(1) 
)
INSERT INTO @data 
VALUES  ( 'Jan' , 'Y' ),
		( 'Feb' , 'N' ),
		( 'Mar' , 'N' ),
		( 'Apr' , 'Y' ),
		( 'May' , 'N' )

; WITH cte (Month_Name, Value, rn) AS 
(
SELECT   
Month_Name,
Value,
ROW_NUMBER() OVER (ORDER BY (CAST('01 ' + Month_Name + '2000' AS DATETIME))) as rn
FROM @data
)

SELECT TOP 1  
	cte1.Month_Name AS [First]
,	cte2.Month_Name AS [Second]	
FROM cte AS cte1
JOIN cte AS  cte2 ON cte1.rn < cte2.rn 
WHERE cte1.Value = 'y' AND cte2.Value = 'Y'
ORDER BY cte1.rn

1) Prepared sample data appreciated

2) Proposed solution expects that exists some kind of guarantee that cte resultset hold maximum two rows with ValueName=Y. Such guarantees can be enforced by UDF on check constraint for this column or table triggers.

3) Proposed solution expects that column month_name have strict checks of month names, udf+ check constraint can be used.

Answer 4

You can make use of PIVOT table.

1. http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/
2. http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

You can filter data in select clause using Where clause.

i.e. WHERE Value='y'

Filter data in pivot with above condition.