SQL Server : how to pivot where new columns have multiple unique values -

using table :

| cust | product | qty | smallest | ----------------------------------- |    e |    1600 |   2 |        1 | |    f |    1600 |   6 |        9 | |    g |    1600 |   1 |        8 | 

i want pivot make this:

|    e    |    f    |   g    | ------------------------------ |    1600 |    1600 |   1600 | |    2    |    6    |   1    | |    1    |    9    |   8    | 

i how 1 unique row. if have qty or have smallest works fine query example:

query

select e, f, g ( select cust, product, qty product) pivot (sum(qty) cust in (e,f,g)) pvt 

output

| e | f | g | ------------- | 2 | 6 | 1 | 

but, if add column concoction in query, mess:

query

select e, f, g ( select cust, product, qty, smallest product) pivot (sum(qty) cust in (e,f,g)) pvt 

output

|      e |      f |      g | ---------------------------- |      2 | (null) | (null) | | (null) | (null) |      1 | | (null) |      6 | (null) | 

and it's easy see why when change query:

query

select product,smallest, e, f, g ( select cust, product, qty, smallest product) pivot (sum(qty) cust in (e,f,g)) pvt 

output

| product | smallest |      e |      f |      g | ------------------------------------------------- |    1600 |        1 |      2 | (null) | (null) | |    1600 |        8 | (null) | (null) |      1 | |    1600 |        9 | (null) |      6 | (null) | 

the pattern becomes obvious. it's finding there 1600 , 1 there 1 e value: 2, there 1600 , 8 there 1 f value 6, etc.

i problem, have no clue how fix it. can aid me in futile quest?

sql fiddle

really want unpivot take care of multiple columns, pivot data. favorite way of doing unpivot use cross apply, can want. such.

select e, f, g ( select cust, columnname, value product cross apply ( values ('product', product),                         ('qty', qty),                         ('smallest',smallest))      unpivoted(columnname, value)) pivot (sum(value) cust in (e,f,g)) pvt 

and of course if want see column values coming from:

select columnname, e, f, g ( select cust, columnname, value product cross apply ( values ('product', product),                         ('qty', qty),                         ('smallest',smallest))      unpivoted(columnname, value)) pivot (sum(value) cust in (e,f,g)) pvt 

edit:

here solution multiple types. have have multiple queries, 1 each data type. among other things have sum in numeric query won't work on varchar columns. output has same. meaning have convert numeric or date columns varchar.

select columnname, cast(e varchar(30)) e, cast(f varchar(30)) f, cast(g varchar(30)) g ( select cust, columnname, value product cross apply ( values ('product', product),                         ('qty', qty),                         ('smallest',smallest))      unpivoted(columnname, value)) pivot (sum(value) cust in (e,f,g)) pvt union select columnname, e, f, g ( select cust, columnname, value product cross apply ( values ('charcola', charcola),                         ('charcolb', charcolb))      unpivoted(columnname, value)) pivot (min(value) cust in (e,f,g)) pvt