SQL Server Performance Forum – Threads Archive

rows into columns

I have a table, tblData that looks like this: DataName,Data
————–
ID,1
Name,Dylan
Position,Network Admin
ID,2
Name,Chip
Position,Network Engineer
ID,3
Name,Ed
Position,Web Developer I have created a temp table, #tmpData, with the columns ID, Name and Position: CREATE TABLE #tmpData(ID int, Name varchar(10), Position varchar(100)) I need to insert all the Data values into the temp table. I know how to do this with a cursor or loop solution, but I was wondering if there was a way to insert all the Data values into the temp table with a single select statement? I would like to know this because this data is not fixed and will vary. "How do you expect to beat me when I am forever?"

---

What aspect of the existing table ties the various rows together ?
Ie, what ties the user ‘Dylan’ to the Position Record containing ‘Network Admin’? You should be able to find a nice solution to the problem assuming you have a key of some sort containing this info. From what I can see it looks like the table just relies purely on the order of the records to ‘group’ rows in a record?

---

Ah yes, good point Chappy, lets say there is a unique ID, so tblData now has: DataName,Data,UID
————–
ID,1,1
Name,Dylan,1
Position,Network Admin,1
ID,2,2
Name,Chip,2
Position,Network Engineer,2
ID,3,3
Name,Ed,3
Position,Web Developer,3
"How do you expect to beat me when I am forever?"

---

Found a solution to do this in a single SELECT. I’ll post the table structure again: TBLDATA: DataID,DataName,DataValue
—————————–
1,Name,Roy
1,Position,Developer
2,Name,John
2,Position,Support Developer
3,Name,Dianna
3,Position,Business Analyst I have the below table that I have to get the data into: TBLNEWDATA: Name,Position
——————-
Roy,Developer
John,Support Developer
Dianna,Business Analyst I wanted a single select to transfer the data from the first table into the second table. Here is the SQL to do this in a single SELECT statement: INSERT INTO TBLNEWDATA(Name,Position)
SELECT
MAX(CASE WHEN DataName=’Name’ THEN DataValue ELSE” END) AS Name,
MAX(CASE WHEN DataName=’Position’ THEN DataValue ELSE” END) AS Position
FROM
TBLDATA
GROUP BY
DataID If the data was dynamic, then you would use dynamic sql.
"How do you expect to beat me when I am forever?"

---

]]>