Prev: insert into syntax error
Next: Forming a date
From: Tarvirdi on 31 May 2010 05:47
Dear friends,
I have a table as bellow
family-id ChildName
1 jack
1 susan
1 john
2 smith
2 jack

i want to see and insert them to other table as
id name1 name2 name3 name4
1 jack susan john <null>
2 smith jack <null> <null>

something like pivot but fields name are fixed and values distributed
between name[n] fields from first field(Name1)
How can I write sql for above conversion?
Thanks
Tarvirdi
From: Uri Dimant on 31 May 2010 05:58
Hi
Assuming you are using SQL Server 2005 and onwards
CREATE TABLE #mable(mid INT, token nvarchar(16))

INSERT INTO #mable VALUES (0, 'foo')
INSERT INTO #mable VALUES(0, 'goo')
INSERT INTO #mable VALUES(1, 'hoo')
INSERT INTO #mable VALUES(1, 'moo')

SELECT m1.mid,
( SELECT m2.token + ','
FROM #mable m2
WHERE m2.mid = m1.mid
ORDER BY token
FOR XML PATH('') ) AS token
FROM #mable m1
GROUP BY m1.mid ;


"Tarvirdi" <m_Tarvirdi(a)isc.iramet.net> wrote in message
news:OGk0NZKALHA.4400(a)TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a table as bellow
> family-id ChildName
> 1 jack
> 1 susan
> 1 john
> 2 smith
> 2 jack
>
> i want to see and insert them to other table as
> id name1 name2 name3 name4
> 1 jack susan john <null> 2 smith jack <null> <null>
>
> something like pivot but fields name are fixed and values distributed
> between name[n] fields from first field(Name1)
> How can I write sql for above conversion?
> Thanks
> Tarvirdi


From: --CELKO-- on 31 May 2010 11:40
Please read any book on SQL and the basics of RDBMS. Fields are not
columns; rows are not records. About chapter 2 or 3, you will learn
about a thing called "Firsts Normal Fomr" (1NF).

In words of Edsgar Dijkstra: "You are doing it completely wrong."
From: Erland Sommarskog on 31 May 2010 17:24
Tarvirdi (m_Tarvirdi(a)isc.iramet.net) writes:
> Dear friends,
> I have a table as bellow
> family-id ChildName
> 1 jack
> 1 susan
> 1 john
> 2 smith
> 2 jack
>
> i want to see and insert them to other table as
> id name1 name2 name3 name4
> 1 jack susan john <null>
> 2 smith jack <null> <null>
>
> something like pivot but fields name are fixed and values distributed
> between name[n] fields from first field(Name1)
> How can I write sql for above conversion?

With the information you have given, there is not really a way to get
exactly the output you ask for, as I can discern no rule that determines
what goes in name1, what goes in name2 etc. But this uses alphabetic
order:

SELECT FamilyID,
name1 = MIN(CASE WHEN rowno = 1 THEN ChildName END),
name2 = MIN(CASE WHEN rowno = 2 THEN ChildName END),
name3 = MIN(CASE WHEN rowno = 3 THEN ChildName END),
name4 = MIN(CASE WHEN rowno = 4 THEN ChildName END)
FROM (SELECT FamilyID, ChildName,
row_number() OVER(PARTITION BY FamilyID ORDER BY ChildName)
FROM tbl) AS d
GROUP BY FamilyID

The solution requires SQL 2005 and makes use of the row_number function
which numbers row within the family. That produces a derived table, and
on this table I run a pivot query. The MIN() and GROUP BY here is only
used toget all values on a single row. Without them, there would be
one row per child.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Plamen Ratchev on 31 May 2010 22:11
Here is similar solution using the PIVOT operator:

SELECT FamilyID,
[1] AS name1,
[2] AS name2,
[3] AS name3,
[4] AS name4
FROM (
SELECT FamilyID, ChildName,
ROW_NUMBER() OVER(PARTITION BY FamilyID ORDER BY ChildName) AS
rn
FROM Foo) AS F
PIVOT
(MAX(ChildName) FOR rn IN ([1], [2], [3], [4])) AS P;

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: insert into syntax error
Next: Forming a date