Pivot Tbl without Aggregation

Question

Hi

I have the following and would like to convert values in the Item Columns into Columns

EncID      Item       Value

1            GlueQty    1

1           GlueExp    02/2/2013

1        GlueBrand    TBH

1            GlueQty    1

1           GlueExp    03/2/2013

1        GlueBrand    TBB

I need a table with the following structure

EntId           GlueQty  GlueExp   GlueBrand

1                        1     2/2/2013    tbh

1                         1    3/2/2013    tbb

Please help

          

---

Thanks Mr Q

Answer 1

Looks like you have an EAV (Entity-Attribute-Design). While there are cases where such a design can be succesful, in many cases it is the result of a lack of ambition for the database design, and you may have to pay a dire price in terms of complex and difficult queries.

A typical pivot query looks like this:

SELECT EntID,
       MIN(CASE Item WHEN 'GlueQty' THEN Value END) AS GlueQty,
       MIN(CASE Item WHEN 'GlueExp' THEN Value END) AS GlueExp,
       MIN(CASE Item WHEN 'GlueBrand' THEN Value END) AS GlueBrand
FROM   tbl
GROUP  BY EntID

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

I think your EntId should be 1 and 2 (1 for the first 3 rows and 2 for next 3 rows). In this case the solution is simple

select * from InfoTable PIVOT (max([Value]) for Item In ([GlueQty],[GlueExp], [GlueBrand])) pvt

If the item names are not known in advance, it is also simple, but we're talking dynamic PIVOT in such case.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

I agree the DB design is poor.

would your solution work if we had more than 1 item for a given entID?

---

Thanks Mr Q

Answer 4

this would not work if a specific EntId we had more than 1 item and we wanted to display all items not just max

---

Thanks Mr Q

Answer 5

Did you try this code? As I said, it will work just fine assuming that EntId is the same for each Item that belongs to this EntId, but different for the next items belonging to different EntId. If it's as you showed in your sample (same EntId for all 6 items) then neither my nor Erland's solution will work.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

both items have the same entID. would you know any other approach that may work?

---

Thanks Mr Q

Answer 7

In this case how will you know how to group them together? You may try then:

SELECT EntID,
       MIN(CASE Item WHEN 'GlueQty' THEN Value END) AS GlueQty,
       MIN(CASE Item WHEN 'GlueExp' THEN Value END) AS GlueExp,
       MIN(CASE Item WHEN 'GlueBrand' THEN Value END) AS GlueBrand
FROM   (select EntId, Item, [Value], ROW_NUMBER() over (order by (select 0)) /%3 as GrpId FROM tbl) X
GROUP  BY EntID, GrpId

in other words, you need to introduce a new Group Id that will group first 3 items together and then next 3 items, etc. If you have identity Id in your table, you can use it in the ORDER BY in the ROW_NUMBER() function as right now I don't know which ORDER BY to use.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

thanks, there isn't a identity ID. I'll try to create a id based on storetime and will see how it goes.

---

Thanks Mr Q

Answer 9

A table is be definition an unordered set of rows.  So, if for some SELECT you want an order, you have to find that order by using data that is in one or more of the columns in that table.  So when you say your table has the following rows

1        GlueQty    1
1        GlueExp    02/2/2013
1        GlueBrand  TBH
1        GlueQty    1
1        GlueExp    03/2/2013
1        GlueBrand  TBB

It would be just as correct to say your table has (note the GlueBrands have been switched)

1        GlueQty    1
1        GlueExp    02/2/2013
1        GlueBrand  TBB
1        GlueQty    1
1        GlueExp    03/2/2013
1        GlueBrand  TBH

When you write those rows in English, you have to write them in order, so you know what goes with what.  But in a table they don't have an order.

So when you want to put them together you have to have a method.

One way is to say you don't care which GlueQty value goes with which GlueBrand value and which GlueExp value.  I'm pretty sure from you question, you do care.  But if you didn't, we could give you a query which will work "randomly" assigning rows together.

The only other choice is for you to have a column (or columns) which allow you to determine which GlueQty value goes with which GlueBrand value and which GlueExp value.  Either because you add some new column or because you already have such a column in your table.

Your last post implies maybe you do have such a column (named storetime).  If you do, you could use that column.  But that will be problematic if there is any chance you could have two rows with the exact same EntID, Item, and storetime (because then you are back to the problem that you don't know the "correct" order).  But if there is no chance you would ever get two rows with the exact same EntID, Item, and storetime, then we can give you a query that will do what you want.  We would need to know whether the various item values that go together all have the same storetime or is it just that the GlueQty row with the smallest storetime value goes with the GlueBrand with the smallest storetime value and the GlueExp with the smallest storetime value (but those storetime values are not necessarily equal), and then you continue with second smallest values, etc.  Either way, we could give you a query that would work.

I have to agree with Erland's comment that this is an EAV design and why there are a few cases where EAV designs are helpful/needed, they are overused.  They tend to cause you to have complex select queries that have major performance problems.

Tom