SQL Programming - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Programming

Author

Topic

kenlok
Starting Member

16 Posts

Posted - 2008-04-02 : 23:53:58

I dont know how to write a SQL Programming code from the table to the table (As the photo)

Would it be possable to help me?

Thanks so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 01:56:20

[code]INSERT INTO NewTable
SELECT t.Order_No,
t.Detail_ID,
t.Shpmt_Id,
t.CSIntId,
t.DetColorId,
t.DetInseamId,
t.PxCode,
LEFT(sl.SizeList,LEN(sl.SizeList)-1)
FROM OldTable t
CROSS APPLY (SELECT Size + ',' AS [text()]
FROM OldTable
WHERE Order_No= t.OrderNo
AND PxCode=t.PxCode
FOR XML PATH('')) sl(SizeList)[/code]

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-03 : 02:19:37

Hi, try with also

declare @StrConcat table (orderno int, deptid int, shpmentid int, col1 nvarchar(10),col2 nvarchar(10))

insert into @StrConcat
select 112233, 3, 4, 'A','S'union all
select 112233, 3, 4,'A','M' union all
select 112233, 3, 4,'A','L' union all
select 112233, 3, 4,'A','XL' union all
select 112233, 3, 4,'A','XXL' union all
select 112233, 3, 4,'B','XXXL' union all
select 112233, 3, 4,'B','XXXXL'

select orderno, deptid, shpmentid, col1, stuff( ( select ', '+ col2 from @StrConcat t1 where t2.col1 = t1.col1 for xml path('')),1,1,'')
from @StrConcat t2
group by orderno, deptid, shpmentid, col1
order by col1

kenlok
Starting Member

16 Posts

Posted - 2008-04-03 : 04:37:12

visakh16, ranganath, Thank you so much

And I have an other questions on it.....

There are different Shpmt_ID and the PxCode with the same Size

Would you mind to help to solve it?

Thank you so much!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-03 : 05:21:15

ok some posting guidelines first:
post your data in the form of table definition and the insert script for the data, NOT pics.
this will provide people trying to answer with easy data to try out the solutions.
do what it says in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-03 : 05:31:13

Hi,

Try with this

declare @StrConcat table (orderno int, deptid int, shpmentid int, col1 nvarchar(10),col2 nvarchar(10))

insert into @StrConcat
select 112233, 3, 4, 'A','S'union all
select 112233, 3, 4,'A','M' union all
select 112233, 3, 4,'A','L' union all
select 112233, 3, 6,'A','XL' union all
select 112233, 3, 6,'A','XXL' union all
select 112233, 3, 4,'B','XXXL' union all
select 112233, 3, 4,'B','XXXXL' Union All
Select 112233, 3, 5, 'C','AAA' Union All
Select 112233, 3, 5, 'C', 'BBB'

select orderno, deptid, shpmentid, col1, stuff( ( select ', '+ col2 from @StrConcat t1 where t2.col1 = t1.col1 and t1.shpmentid = t2.shpmentid for xml path('')),1,1,'')
from @StrConcat t2
group by orderno, deptid, shpmentid, col1
order by col1

kenlok
Starting Member

16 Posts

Posted - 2008-04-04 : 07:33:09

Thanks ranganath

But my data is come from a table....
I dont know how to using "union all" when I get the data.

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 07:37:43

the 'union all' was just where ranganath was setting up some test data (which is what spirit1 asked you to do by the way).

the bit your interested in is just the query at the end

Em

kenlok
Starting Member

16 Posts

Posted - 2008-04-06 : 23:51:59

Hello~ My Table is

Create table #MuPxTemp(
Order_No varchar(20), Det_Id int, Shpmt_Id int,
Lot varchar(3), PxCode_shpmt char(1),
PxCodeChanged char(1), Qty int, PxCode char(1),
DetColorId int, ColorSeq int, CustPx decimal(12,4),
FtyPx decimal(12,4), Size varchar(7)
)

Create table #MuPxTemp2(
Order_No varchar(20), Det_Id int, Shpmt_Id int,
Lot varchar(3), PxCode_shpmt char(1),
PxCodeChanged char(1), Qty int, PxCode char(1),
DetColorId int, ColorSeq int, CustPx decimal(12,4),
FtyPx decimal(12,4), Size varchar(7)
)

INSERT INTO #MuPxTemp2
SELECT t.Order_No,
t.Det_ID,
t.Shpmt_Id,
t.DetColorId,
t.PxCode,
LEFT(sl.SizeList,LEN(sl.SizeList)-1)
FROM #MuPxTemp t
CROSS APPLY (SELECT Size + ',' AS 'Size'
FROM #MuPxTemp
WHERE Order_No= t.Order_No
AND PxCode=t.PxCode
FOR XML PATH('')) sl(SizeList)

But there are some error "Insert Error: Column name or number of supplied values does not match table definition."

Please help!

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-07 : 02:57:48

the error message is really very descriptive. your are inserting into your temp table but not supplying the same number of columns etc. you'll need to specify which columns you are actually inserting, and in the same order as your select statement.

insert into #MuPxTemp2 (Order_no, Det_ID, Shpmt_Id, DetColorId, PxCode, Size)
select t.Order_no,
etc...
etc...

Em

kenlok
Starting Member

16 Posts

Posted - 2008-04-10 : 04:44:21

Create table #MuPxTemp(
Order_No varchar(20), Det_Id int, Shpmt_Id int,
Lot varchar(3), PxCode_shpmt char(1),
PxCodeChanged char(1), Qty int, PxCode char(1),
DetColorId int, ColorSeq int, CustPx decimal(12,4),
FtyPx decimal(12,4), Size varchar(7)
)

Create table #MuPxTemp2(
Order_No varchar(20), Det_Id int, Shpmt_Id int,
Lot varchar(3), PxCode_shpmt char(1),
PxCodeChanged char(1), Qty int, PxCode char(1),
DetColorId int, ColorSeq int, CustPx decimal(12,4),
FtyPx decimal(12,4), Size varchar(7)
)

INSERT INTO #MuPxTemp2
(Order_No, Det_Id, Shpmt_Id, DetColorId, PxCode, Size)
SELECT t.Order_No,
t.Det_ID,
t.Shpmt_Id,
t.DetColorId,
t.PxCode,
LEFT(sl.SizeList,LEN(sl.SizeList)-1)
FROM #MuPxTemp t
CROSS APPLY (SELECT Size + ',' AS 'Size'
FROM #MuPxTemp
WHERE Order_No= t.Order_No
AND PxCode=t.PxCode
FOR XML PATH('')) sl(SizeList)

Results
---------------------------------------
(0 row(s) affected)
Msg 8152, Level 16, State 10, Line 64
String or binary data would be truncated.
The statement has been terminated.

Is the data is so large? or the data type problems?
Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources