How can I convert to horizontal records the following data:

party_id name_type name
-------- --------- ---------------------
1 lnme Smith
1 nme Jhon
1 alias Jhonny
2 lnme Hernandez
2 nme Fredy
2 title Mr.

I want to have the data as follow

party Id title lnme nme alias
-------- ----- ----------- ----------- ------
1 Smith Jhon Jhonny
2 Mr. Hernandez Fredy

If you use SQL 2005 beta2, you should be able to use PIVOT. An example is:

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4,
[233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

--
Peter Kim
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Hello, I've done something like this before, you can create a temp table for
your output (If you know how many possible name_type values there are),
and then do an update using a:

Create #tmpOut with all required fields (Each possible name_type field)

--seed the table with the appropriate party_id's
insert into #tmpOut (party_ID) (select distinct Party_id from mainTable)


update #tmpOut set
lnme =
case name_type
when 'lnme' then
mainTable.name
else
#tmpOut.lname
end
....repeat for each field
from mainTable where party_id=#tmpOut.party_id


Or something along those lines, (I don't have time to actually test it out,
and it probably has syntax errors. I've been doing VB6, VB.Net, C#, and
Java recently, and I get the syntax screwed up all the time if I don't have
the IDE running, and showing me the pretty colors sorry :-)

Also, you may be able to dynamically create the table, and the appropriate
Case statements for each field, and then run it as a Dynamic SQL using Exec
@SQLQuery, I doubt the performance would be too bad.

Also, you can probably do something similar using OLAP, but I haven't used
OLAP for several years, and I don't recall how to do all the stuff.

I have never used SQL 2005 Beta, but if it does Pivot Tables, that will be
great! I can't wait to try it out.

I bet there are 50 ways to do it better than I've told you, but hopefully
this will give you a couple ideas to get started anyway.

Good Luck

Lance



[quoted text, click to view]
How can I convert to horizontal records the following data:

party_id name_type name
-------- --------- ---------------------
1 lnme Smith
1 nme Jhon
1 alias Jhonny
2 lnme Hernandez
2 nme Fredy
2 title Mr.

I want to have the data as follow

party Id title lnme nme alias
-------- ----- ----------- ----------- ------
1 Smith Jhon Jhonny
2 Mr. Hernandez Fredy

Lance,

A very nice and compact way to do it is with the following pattern:

SELECT PKey, --Primary key
MAX(CASE YourDecisionColumn
WHEN 'A' THEN YourValueColumn
ELSE NULL
END) AS A,
MAX(CASE YourDecisionColumn
WHEN 'B' THEN YourValueColumn
ELSE NULL
END) AS B,
...
FROM YourTable
GROUP BY PKey

This would tabulate the following table:

PKey YourDecisionColumn YourValueColumn
---------------------------------------------------------
1 A AValCol1
1 B BValCol1
2 A AValCol1
2 B BValCol1


into:


PKey A B
----------------------------------
1 AValCol1 BValCol1
2 AValCol2 BValCol2


Depending on your indexes, this method can perform quite a bit better than
other methods that use temp tables or correlated subqueries.


[quoted text, click to view]

Excellent! I learn something new every day on these NG's!

Thanks
Lance

[quoted text, click to view]
Lance,

A very nice and compact way to do it is with the following pattern:

SELECT PKey, --Primary key
MAX(CASE YourDecisionColumn
WHEN 'A' THEN YourValueColumn
ELSE NULL
END) AS A,
MAX(CASE YourDecisionColumn
WHEN 'B' THEN YourValueColumn
ELSE NULL
END) AS B,
...
FROM YourTable
GROUP BY PKey

This would tabulate the following table:

PKey YourDecisionColumn YourValueColumn
---------------------------------------------------------
1 A AValCol1
1 B BValCol1
2 A AValCol1
2 B BValCol1


into:


PKey A B
----------------------------------
1 AValCol1 BValCol1
2 AValCol2 BValCol2


Depending on your indexes, this method can perform quite a bit better than
other methods that use temp tables or correlated subqueries.


[quoted text, click to view]