Issue in Unpivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Issue in Unpivot

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-21 : 04:42:38

HI,

I have a table in which for a particular row there are columns like

NOFYEAR01-NOFYEAR05 and CMAX01-CMAX06

When i did UNPIVOT like

UNPIVOT(NOFYEAR FOR ITEM IN( NOFYEAR01,NOFYEAR02,NOFEAR03,NOFYEAR04,NOFYEAR05)) AS NOFYEAR
UNPIVOT(CMAX FOR ITEM1 IN( CMAX01,CMAX02,CMAX03,CMAX04,CMAX05,CMAX06)) AS CMAX

I am getting only 5 rows and the Value of CMAX06 is truncated.
Can any one tell me how to get that column also into my row set

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:01:20

please show some sample data and then explain how its getting truncated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-22 : 06:36:23

ok....

I have written query as follows

SELECT AGEMIN, AGEMAX, ZMINFACT FROM

(SELECT * from [dbo].dbo_TJ667F where [ITEMITEM] = 'FU1BA') PRODRULE

UNPIVOT (AGEMIN FOR ITEM IN ([AGEMIN01],[AGEMIN02], [AGEMIN03], [AGEMIN04], [AGEMIN05], [AGEMIN06], [AGEMIN07], [AGEMIN08], [AGEMIN09], [AGEMIN10] )) AS AGEMIN

UNPIVOT (AGEMAX FOR ITEM1 IN ([AGEMAX01],[AGEMAX02], [AGEMAX03],[AGEMAX04],[AGEMAX05],[AGEMAX06],[AGEMAX07],[AGEMAX08],[AGEMAX09],[AGEMAX10]) ) AS AGEMAX

UNPIVOT (ZMINFACT FOR ITEM2 IN ([ZMINFACT01],[ZMINFACT02], [ZMINFACT03],[ZMINFACT04],[ZMINFACT05],[ZMINFACT06],[ZMINFACT07],[ZMINFACT08],[ZMINFACT09],[ZMINFACT10],[ZMINFACT11]) ) AS ZMINFACT

WHERE RIGHT(ITEM,2)= RIGHT(ITEM1,2) AND
RIGHT(ITEM1,2)= RIGHT(ITEM2,2)

If you observe there are 11 columns with ZMINFACT and for other columns there are only 10. When i execute this query i m geting only 10 rows and m not getting the 11th row for ZMINFACT11.

GEMIN AGEMAX ZMINFACT
--------------------------------------- --------------------------------------- ---------------------------------------
0 30 5.00000
31 65 5.00000
0 0 0.00000
0 0 0.00000
0 0 0.00000
0 0 0.00000
0 0 0.00000
0 0 0.00000
0 0 0.00000
0 0 0.00000

Can u pls tell me how to fetch that 1th one also

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 06:42:36

i think reason is current way its written it applies one UNPIVOT over unpivoted result. i think you may be better off seperating the UNPIVOTS into seperate queries and then join then using common fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-12-22 : 07:01:08

Tried this?

SELECT		m1.AGEMIN,
		m2.AGEMAX,
		m3.ZMINFACT
FROM		dbo.dbo_TJ667F AS s
CROSS APPLY	(
			VALUES	(0, AGEMIN01),
				(1, AGEMIN02),
				(2, AGEMIN03),
				(3, AGEMIN04),
				(4, AGEMIN05),
				(5, AGEMIN06),
				(6, AGEMIN07),
				(7, AGEMIN08),
				(8, AGEMIN09),
				(9, AGEMIN10),
				(10, NULL)
		) AS m1(rid, AGEMIN)
CROSS APPLY	(
			VALUES	(0, AGEMAX01),
				(1, AGEMAX02),
				(2, AGEMAX03),
				(3, AGEMAX04),
				(4, AGEMAX05),
				(5, AGEMAX06),
				(6, AGEMAX07),
				(7, AGEMAX08),
				(8, AGEMAX09),
				(9, AGEMAX10),
				(10, NULL)
		) AS m2(rid, AGEMAX)
CROSS APPLY	(
			VALUES	(0, ZMINFACT01),
				(1, ZMINFACT02),
				(2, ZMINFACT03),
				(3, ZMINFACT04),
				(4, ZMINFACT05),
				(5, ZMINFACT06),
				(6, ZMINFACT07),
				(7, ZMINFACT08),
				(8, ZMINFACT09),
				(9, ZMINFACT10),
				(10, ZMINFACT11)
		) AS m3(rid, ZMINFACT)
WHERE	s.ITEMITEM = 'FU1BA'
	AND RIGHT(s.ITEM, 2) = RIGHT(s.ITEM1, 2)
	AND RIGHT(s.ITEM1, 2) = RIGHT(s.ITEM2, 2)
	AND m1.rid = m2.rid
	AND m2.rid = m3.rid

N 56°04'39.26"
E 12°55'05.63"

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-12-22 : 23:44:04

Thanks.... Peter Larsson
The above query is returning all that i need.
But can u pls tell me what is 'rid' here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 01:32:44

quote:
Originally posted by Sql_forum

Thanks.... Peter Larsson
The above query is returning all that i need.
But can u pls tell me what is 'rid' here.

rid corresponds to id field having integer values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources