consolidate by column value - 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)

consolidate by column value

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2014-07-02 : 09:26:14

BBarn
Starting Member

14 Posts

Posted - 2014-07-02 : 10:03:54

[code]
DECLARE @MyTable TABLE (PRODUCT_NO VARCHAR(4),
PRODUCT_NAME VARCHAR(30),
TRACTOR_NAME VARCHAR(30),
TRACTOR_MODEL VARCHAR(30),
MODEL_YEAR INTEGER
)

INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac VC',2001)
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac VC',2002)
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac VC',2003)
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac VC',2006)
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac AX',2001)
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac AX',2002)
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac AX',2003)

select PRODUCT_NO,
PRODUCT_NAME,
TRACTOR_NAME,
TRACTOR_MODEL,
CAST(MIN(MODEL_YEAR) as varchar) + '-' + CAST(MAX(MODEL_YEAR) as varchar) as YRS_SPAN
from @MyTable
GROUP BY PRODUCT_NO,PRODUCT_NAME,TRACTOR_NAME,TRACTOR_MODEL

[/code]

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2014-07-02 : 11:44:38

Thank you for your suggestion. This is almost what I was looking for.
However, I only want to consolidate MODEL_NAME for consecutive years.
This query returns:
PRODUCT_NO | PRODUCT_NAME | TRACTOR_NAME | TRACTOR_MODEL | YRS_SPAN
4321 | valve 2x | Claas Xerion 3800 | Trac AX | 2001-2003
4321 | valve 2x | Claas Xerion 3800 | Trac VC | 2001-2006

But the Claas Xerion 3800 Trac VC is only valid from 2001 to 2003 and in 2006, not in 2004 and 2005 so the result 2001-2006 is not valid.

What I need is to consolidate each record for uninterrupted year span.

Since the Trac VC and Trac AX both fit on the TRACTOR_NAME Claas Xerion 380 for the year span of 2001 to 2003 I need that results to look like:
4321 | valve 2x | Claas Xerion 3800 | Trace VX, Trac AX | 2001-2003
4321 | valve 2x | Claas Xerion 3800 | Trac VC | 2006

since the Trac VC for that product number only fits on 2006. The AX doesn't and there are no models for 2004 and 2005.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-02 : 14:19:27

Not sure if this will work with real data, but it works with your sample data:

DECLARE @MyTable TABLE (PRODUCT_NO VARCHAR(4),
                        PRODUCT_NAME VARCHAR(30),
                        TRACTOR_NAME VARCHAR(30),
                        TRACTOR_MODEL VARCHAR(30),
                        MODEL_YEAR INTEGER
                        )
                        
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac VC',2001) 
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac VC',2002) 
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac VC',2003) 
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac VC',2006) 
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac AX',2001) 
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac AX',2002) 
INSERT INTO @MyTable values('4321','valve 2x','Claas Xerion 3800','Trac AX',2003)

;WITH DateCte AS
(
	SELECT 
		MIN(MODEL_YEAR) AS MIN_MODEL_YEAR, 
		MAX(MODEL_YEAR) AS MAX_MODEL_YEAR 
	FROM @MyTable
),
Date2Cte AS
(
	SELECT S.Number
	FROM master..spt_values AS S
	WHERE S.number BETWEEN (SELECT MIN_MODEL_YEAR FROM DateCte) AND (SELECT MAX_MODEL_YEAR FROM DateCte)
)
,Cte AS 
(
SELECT 
	*
	,DENSE_RANK() OVER (ORDER BY Cte.Number) - ROW_NUMBER() OVER (PARTITION BY PRODUCT_NO, PRODUCT_NAME, TRACTOR_MODEL ORDER BY Cte.Number) AS GroupNum
FROM
	Date2Cte AS Cte
LEFT OUTER JOIN
	@MyTable
	ON Cte.number = MODEL_YEAR
)

SELECT 
	PRODUCT_NO,
    PRODUCT_NAME,
    TRACTOR_NAME,
    STUFF((SELECT DISTINCT ',' + TRACTOR_MODEL FROM Cte AS A WHERE A.GroupNum = Cte.GroupNum FOR XML PATH('')), 1, 1, '') AS TRACTOR_MODEL,
	CASE
		WHEN MIN(MODEL_YEAR) = MAX(MODEL_YEAR) THEN CAST(MAX(MODEL_YEAR) AS VARCHAR(20))
		ELSE CAST(MIN(MODEL_YEAR) AS VARCHAR(20)) + '-' + CAST(MAX(MODEL_YEAR) AS VARCHAR(20)) 
	END as YRS_SPAN
FROM 
	Cte AS Cte
WHERE 
	PRODUCT_NAME IS NOT NULL
GROUP BY 
	GroupNum,
	PRODUCT_NO,
	PRODUCT_NAME,
	TRACTOR_NAME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources