Database

create table #test
(
P_ID int  null,
Pname ntext(10000) null
)

Msg 131, Level 15, State 2, Line 5<br />
The size (10000) given to the column 'Pname' exceeds the maximum allowed for any data type (8000).

create table #b(a ntext)

CREATE TABLE BulkTest (
Column1 bigint,
Column2 bigint)

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NativeFixed" LENGTH="7"/>
  <FIELD ID="2" xsi:type="NativeFixed" LENGTH="1"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Column1" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="2" NAME="Column2" xsi:type="SQLBIGINT"/>
 </ROW>
</BCPFORMAT>

BULK INSERT BulkTest FROM 'f:\orders\lineitem.tbl' WITH (FORMATFILE='f:\orders\BulkInsertFormat.txt')

SimulationofSai wrote:

Make an attempt yourself, explain what you've done, and then ask here if you still face any hurdles. We'll be more than willing to help if you've a genuine problem in your hands.

CREATE TABLE #T (ID varchar(2), Symbol varchar(1)); 
INSERT INTO #T(ID, Symbol) SELECT '01', 'A'
INSERT INTO #T(ID, Symbol) SELECT '01', 'B'
INSERT INTO #T(ID, Symbol) SELECT '01', 'C'
INSERT INTO #T(ID, Symbol) SELECT '02', 'B'
INSERT INTO #T(ID, Symbol) SELECT '02', 'D'
--
SELECT 
  ID, 
  STUFF( 
    <code>(SELECT ' and ' + Symbol FROM #T WHERE ID = t.ID FOR XML PATH(''))</code> 
    , 1 
    , 5
    , '' 
  ) AS Symbols
FROM #T AS t
GROUP BY ID
--
DROP TABLE #T

SELECT   ID
,Symbol=STUFF(dbo.Concatenate(' and '+Symbol),1,5,'')
FROM Symbol 
GROUP BY ID
ORDER BY ID
,Symbol