April 29, 2008 at 10:55 am
I have a query issue and I do not even know what to call this. I need to convert one column with multiple values and moved those values to appropriate columns in one row. I have the following data.
Order # Seq# Description
1 1 PO#123
1 2 Tag#ABC
1 3 Item#1
2 1 PO#456
2 2 Tag#DEF
2 3 Item#2
3 1 PO#789
3 2 Tag#GHI
3 3 Item#1
What I need is to convert it to
Order# PO# Tag# Item#
1 PO#123 Tag#ABC Item#1
2 PO#456 Tag#DEF Item#2
3 PO#789 Tag#GHI Item#1
I'm trying to wrap my head around this one. I am thinking some form of a CASE WHEN statement. Any suggestions on where to start on this one?
Thanks
Adam
April 29, 2008 at 11:25 am
The use of an aggregate such as MAX that includes CASE statements is one way of doing this. If you are using SQL Server 2005 you might want to also give a look at the PIVOT operator.
April 29, 2008 at 11:37 am
Because of the sequence number (and assuming it is correct that I can assume it positions the expected column values) you can do this with sub-queries. I used CTE's to make it look nice, but they are totally unnecessary.
[font="Courier New"]CREATE TABLE #tmp (OrderID INT, Seq INT, Descr VARCHAR(10))
INSERT #tmp VALUES (1,1,'PO#123')
INSERT #tmp VALUES (1,2,'Tag#ABC')
INSERT #tmp VALUES (1,3,'Item#1')
INSERT #tmp VALUES (2,1,'PO#124')
INSERT #tmp VALUES (2,2,'Tag#ABC')
INSERT #tmp VALUES (2,3,'Item#2')
GO
; WITH PO (OrderID, Descr)
AS (SELECT OrderID, Descr FROM #tmp WHERE Seq = 1)
, Tag (OrderID, Descr)
AS (SELECT OrderID, Descr FROM #tmp WHERE Seq = 2)
, Item (OrderID, Descr)
AS (SELECT OrderID, Descr FROM #tmp WHERE Seq = 3)
, Orders (OrderID)
AS (SELECT DISTINCT OrderID FROM #tmp)
SELECT
O.OrderID
, P.Descr AS PO
, T.Descr AS Tag
, I.Descr AS Item
FROM
Orders O
INNER JOIN PO P ON P.OrderID = O.OrderID
INNER JOIN Tag T ON T.OrderID = O.OrderID
INNER JOIN Item I ON I.OrderID = O.OrderID[/font]
April 29, 2008 at 11:38 am
A couple of examples of how to do this might look something like:
( Order# integer,
Seq# integer,
Description varchar(12)
)
insert into @Eav
select 1, 1, 'PO#123' union all
select 1, 2, 'Tag#ABC' union all
select 1, 3, 'Item#1' union all
select 2, 1, 'PO#456' union all
select 2, 2, 'Tag#DEF' union all
select 2, 3, 'Item#2' union all
select 3, 1, 'PO#789' union all
select 3, 2, 'Tag#GHI' union all
select 3, 3, 'Item#1'
--select * from @Eav
select
Order#,
max(case when Seq# = 1 then Description else '' end)
as PO#,
max(case when Seq# = 2 then Description else '' end)
as Tag#,
max(case when Seq# = 3 then Description else '' end)
as Item#
from @Eav
group by Order#
select
Order#,
[1] as [PO#],
[2] as [Tag#],
[3] as [Item#]
from @Eav
pivot (max(description) for Seq# in ([1], [2], [3])) as pv
/* -------- Sample Output: --------
Order# PO# Tag# Item#
----------- ------------ ------------ ------------
1 PO#123 Tag#ABC Item#1
2 PO#456 Tag#DEF Item#2
3 PO#789 Tag#GHI Item#1
*/
These kinds of operations are typical of an EAV table. You need to be sure that an EAV type table really fits your needs because EAV tables will tend to add a lot of drag to queries.
April 29, 2008 at 12:11 pm
You guys are awesome. Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply