Summarising data - 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)

Summarising data

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-10-27 : 09:36:06

[code]
DECLARE @MyData TABLE
(
OrderID INT NOT NULL,
OrderItem INT NOT NULL,
PersonaliseItem INT NOT NULL,
PersonaliseID INT NOT NULL,
OrderValue VARCHAR(100) NULL
)

INSERT INTO @MyData
-- First item, single personalisation
SELECT 10372, 1, 1, 10116, 'xxx' UNION ALL

-- Second item, three personalisations
-- First personalisation:
SELECT 10372, 3, 9, 10031, 'Value1' UNION ALL
SELECT 10372, 3, 9, 10032, 'Value2' UNION ALL
-- Second personalisation:
SELECT 10372, 3, 10, 10037, 'Value3' UNION ALL
SELECT 10372, 3, 10, 10038, NULL UNION ALL
-- Third personalisation:
SELECT 10372, 3, 12, 10059, NULL UNION ALL
SELECT 10372, 3, 12, 10064, 'Value4'

SELECT *
FROM @MyData
[/code]
Required output:
[code]
OrderID OrderItem CompositeValue
10372 1 xxx
10372 2 Value1 ..., Value3, Value4
[/code]

Explanation:

Tables are for

Order Header
Order Item
Personalisation Items

Order header has 1:N Order Items
Order Items has 0:N Personalisation items

So I might buy 3 Mugs and want "Kristen" on all of them (in which case I add just one Personalisation item), or I might want the Mugs to say "Kristen", "Graz" and "Tara" - in which case I create 3 personalisation items.

Personalisation can have more than one field - so I might want:

Kristen + Test
Graz + Admin
Tara + Princess

I want to display "Kristen ..., Graz ..., Tara ..." on the [each] Order Item - i.e. the first field of data from each personalisation item, but an "..." ellipsis to indicate that there were more fields present (but not if all additional fields were NULL)

The idea is to give the Shopper an indication of what they choose before they actually commit.

I'm happy to create a temporary table that has one row per Order Item and concatenate the data there, but I'm not sure of the best way to go about it.

Variations-on-a-theme will do, doesn;t have to be exactly like this - e.g. if an alternative, more efficient, query is available

Please note that Order Item and Personalisation Item are not necessarily contiguous (so cannot refer to previous item using ThisItemID-1 !!)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-27 : 17:36:27

maybe this?

SELECT OrderID, OrderItem,
(SELECT OrderValue + ','
FROM @MyData xm where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem
FOR XML PATH ('')) as val
FROM @MyData a
GROUP BY OrderItem, OrderID

If you don't have the passion to help people, you have no passion

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-28 : 02:20:47

Another version that closely matches with expected result


SELECT 
	OrderID, 
	row_number() over (order by orderitem) as orderitem, 
	(
	SELECT case when row_number() over (partition by orderitem order by orderitem) in (1,count(1) over ()) then  OrderValue + ',' else '.' end
	FROM 
		@MyData xm where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem
		FOR XML PATH ('')
	) as CompositeValue
FROM 
	@MyData a
GROUP BY OrderItem, OrderID

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-10-28 : 03:28:39

Blimey! I find use of XML PATH as perverse for this type of job, but maybe that's just me an my brain not as agile as you lot.

I was going to ask if you could both start full time on Monday! but ...


Required output:

OrderID     OrderItem   CompositeValue
10372       1           xxx
10372       2 3         Value1 ..., Value3, Value4

Well, first of all I've sacked myself as the OrderItem was supposed to match the original. Extra points to Madhi for generating the "Required output", but it wasn't what I wanted

and points to yosiasz for anticipating what I actually wanted!


OrderID     OrderItem   val
----------- ----------- -----------
10372       1           xxx,
10372       3           Value1,Value2,Value3,Value4,

I need to suppress additional items for a given OrderItem (in my example "Value1 ...," indicates that only the first value should be shown - and, ideally, there should be some sort of indicator that there are other values - in my example the " ...")

Point deducted for the trailing comma - although I think I'm competent to take care of that myself!


OrderID     orderitem            CompositeValue
----------- -------------------- ---------------
10372       1                    xxx,
10372       2                    Value1,....Value4,

What happened to "Value3" Madhi?

Although, in the real world, I don't think we will get a situation where the FIRST value for an OrderItem is NULL ... but it would be good to allow for it if&when it does happen.

I could do with "Value1 ....," rather than "Value1,...." too I'm afraid.

If I understood the first thing about XML PATH I'd be able to run with this for myself, I'm sure. Chuck in Madhi's nested row_number()'s and a Partition over an IN list include COUNT()'s and I'm completely lost

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-28 : 06:29:22

See if this works for all set of data


SELECT 
	OrderID, 
	orderitem, 
	stuff((
	SELECT case when row_number() over (partition by orderitem order by orderitem) in (1,total-1,total) then  ','+OrderValue else '..' end
	FROM @mydata a where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem and ordervalue is not null
		FOR XML PATH ('')
	),1,1,'') as CompositeValue
FROM 
	(select *, (select count(*) from @MyData where OrderID=t.OrderID and orderitem=t.orderitem and ordervalue is not null) as total from @mydata as t) xm
where ordervalue is not null
GROUP BY OrderItem, OrderID,total

Madhivanan

Failing to plan is Planning to fail

parody
Posting Yak Master

111 Posts

Posted - 2010-10-28 : 06:38:34

Another variation...

;WITH Sub AS
	(
		SELECT
			*
			,row_number() OVER (PARTITION BY OrderID,OrderItem,PersonaliseItem ORDER BY PersonaliseItem ASC) AS FindFirstInGroup
			,count(0) OVER (PARTITION BY OrderID,OrderItem,PersonaliseItem) AS QtyInGroup
			,row_number() OVER (PARTITION BY OrderID,OrderItem ORDER BY PersonaliseItem DESC) AS FindLast
		FROM @MyData
		WHERE OrderValue IS NOT NULL
	)

SELECT 
	Sub1.OrderID
	,Sub1.OrderItem
	,(SELECT 
		CASE
			WHEN FindFirstInGroup = 1 AND QtyInGroup = 1 AND FindLast = 1 THEN OrderValue
			WHEN FindFirstInGroup = 1 AND QtyInGroup <> 1 AND FindLast = 1 THEN OrderValue + '...'
			WHEN FindFirstInGroup = 1 AND QtyInGroup = 1 AND FindLast <> 1 THEN OrderValue + ', '
			WHEN FindFirstInGroup = 1 AND QtyInGroup <> 1 AND FindLast <> 1 THEN OrderValue + '..., '
			ELSE ''
		END
	FROM Sub Sub2 WHERE Sub1.OrderID = Sub2.OrderID AND Sub1.OrderItem = Sub2.OrderItem 
	ORDER BY Sub2.PersonaliseItem ASC FOR XML PATH('')) AS Array
FROM Sub Sub1 
GROUP BY 
	Sub1.OrderID
	,Sub1.OrderItem

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-03 : 04:34:07

Is this solved?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-03 : 04:42:22

Another version


SELECT 
	OrderID, 
	orderitem, 
	stuff((
	SELECT case when row_number() over (partition by orderitem order by orderitem) in (1,count(1) over (partition by orderitem)-1,count(1) over (partition by orderitem)) then  ','+OrderValue  else '..' end
	FROM 
		@MyData xm where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem and Ordervalue is not null
		FOR XML PATH ('')
	),1,1,'') as CompositeValue
FROM 
	@MyData a
GROUP BY OrderItem, OrderID

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-08 : 05:44:05

Kristen, any updates on this?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-08 : 06:21:30

I see only one that works...

INSERT INTO @MyData
SELECT 10372, 3, 8, 10030, 'Me & You'

-- Peso
SELECT		oi.OrderID,
		oi.OrderItem,
		STUFF(pov.Data, 1, 2, '') AS CompositeValue
FROM		(
			SELECT DISTINCT	OrderID,
					OrderItem
			FROM		@MyData
		) AS oi
OUTER APPLY	(
			SELECT	(
					SELECT DISTINCT	', ' + x.OrderValue AS [data()]
					FROM		@MyData AS x
					WHERE		x.OrderID = oi.OrderID
							AND x.OrderItem = oi.OrderItem
					ORDER BY	', ' + x.OrderValue
					FOR XML		PATH(''),
							TYPE
				).value('.', 'VARCHAR(MAX)')
		) AS pov(Data)

-- Yosiasz
SELECT OrderID, OrderItem, 
(SELECT OrderValue + ','
FROM @MyData xm where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem
FOR XML PATH ('')) as val
FROM @MyData a
GROUP BY OrderItem, OrderID

-- Madhivanan 1
SELECT 
	OrderID, 
	row_number() over (order by orderitem) as orderitem, 
	(
	SELECT case when row_number() over (partition by orderitem order by orderitem) in (1,count(1) over ()) then  OrderValue + ',' else '.' end
	FROM 
		@MyData xm where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem
		FOR XML PATH ('')
	) as CompositeValue
FROM 
	@MyData a
GROUP BY OrderItem, OrderID

-- Madhivanan 2
SELECT 
	OrderID, 
	orderitem, 
	stuff((
	SELECT case when row_number() over (partition by orderitem order by orderitem) in (1,total-1,total) then  ','+OrderValue else '..' end
	FROM @mydata a where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem and ordervalue is not null
		FOR XML PATH ('')
	),1,1,'') as CompositeValue
FROM 
	(select *, (select count(*) from @MyData where OrderID=t.OrderID and orderitem=t.orderitem and ordervalue is not null) as total from @mydata as t) xm
where ordervalue is not null
GROUP BY OrderItem, OrderID,total

-- parody
;WITH Sub AS
	(
		SELECT
			*
			,row_number() OVER (PARTITION BY OrderID,OrderItem,PersonaliseItem ORDER BY PersonaliseItem ASC) AS FindFirstInGroup
			,count(0) OVER (PARTITION BY OrderID,OrderItem,PersonaliseItem) AS QtyInGroup
			,row_number() OVER (PARTITION BY OrderID,OrderItem ORDER BY PersonaliseItem DESC) AS FindLast
		FROM @MyData
		WHERE OrderValue IS NOT NULL
	)

SELECT 
	Sub1.OrderID
	,Sub1.OrderItem
	,(SELECT 
		CASE
			WHEN FindFirstInGroup = 1 AND QtyInGroup = 1 AND FindLast = 1 THEN OrderValue
			WHEN FindFirstInGroup = 1 AND QtyInGroup <> 1 AND FindLast = 1 THEN OrderValue + '...'
			WHEN FindFirstInGroup = 1 AND QtyInGroup = 1 AND FindLast <> 1 THEN OrderValue + ', '
			WHEN FindFirstInGroup = 1 AND QtyInGroup <> 1 AND FindLast <> 1 THEN OrderValue + '..., '
			ELSE ''
		END
	FROM Sub Sub2 WHERE Sub1.OrderID = Sub2.OrderID AND Sub1.OrderItem = Sub2.OrderItem 
	ORDER BY Sub2.PersonaliseItem ASC FOR XML PATH('')) AS Array
FROM Sub Sub1 
GROUP BY 
	Sub1.OrderID
	,Sub1.OrderItem

-- Madhivanan 3
SELECT 
	OrderID, 
	orderitem, 
	stuff((
	SELECT case when row_number() over (partition by orderitem order by orderitem) in (1,count(1) over (partition by orderitem)-1,count(1) over (partition by orderitem)) then  ','+OrderValue  else '..' end
	FROM 
		@MyData xm where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem and Ordervalue is not null
		FOR XML PATH ('')
	),1,1,'') as CompositeValue
FROM 
	@MyData a
GROUP BY OrderItem, OrderID

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

Kristen
Test

22859 Posts

Posted - 2010-11-08 : 07:46:12

You guys have probably solved it, but very busy at work and not had time to implement. In the interim we went with "xxx, ..." that just showed the first entry.

I've kept this post open to remind me to come back and tidy up properly ASAP. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources