Dynamic pivot that check the split

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic pivot that check the split

Author

Topic

lishirries
Starting Member

2 Posts

Posted - 2012-04-12 : 08:02:05

Hi,Please help ive been struggling for the past 2 weeks.
Have this data
if split is 1 then puy name in name1.
if split is 2 people then put name in name1 and name2.
if split is 3 then put name in name1,name2 and name3.

Id namel split amount
1 lindy 1 100
2 anna 0.5 50
2 sbu 0.5 50
3 tol 0.25 25
3 inid 0.25 25
3 tok 0.5 25
4 eywu 1 25

want

Id namel name2 name3 amount
1 lindy 100
2 anna sbu 50
3 tol inid tok 25
4 eywu 25

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-04-12 : 08:53:30

Declare @tbl1 table
(ID INT,
name varchar(50),
amount float
)

INSERT INTO @tbl1
SELECT 1,'lindy', 100

INSERT INTO @tbl1
SELECT 2,'anna ', 50

INSERT INTO @tbl1
SELECT 2,'sbu', 50

INSERT INTO @tbl1
SELECT 3,'tol', 25

INSERT INTO @tbl1
SELECT 3,'tok', 25

INSERT INTO @tbl1
SELECT 3,'inid', 25

INSERT INTO @tbl1
SELECT 4,'eywu', 25

SELECT DISTINCT A.ID, STUFF((SELECT ' ' + B.name FROM @TBL1 B WHERE B.ID = A.ID FOR XML PATH('')), 1, 1, '') AS name, amount FROM @TBL1 A

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

lishirries
Starting Member

2 Posts

Posted - 2012-04-12 : 09:00:02

Thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources