"Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
news:CqSdnYKLwYzFNHzfRVn-3A@kconline.com...
>I have a SQL table defined like this.
>
> Item Varchar(30)
> Type Varchar(10)
> Lang Char(2)
> Page Char(1)
> Comments Varchar(75)
>
> Delimited data from the table
>
> Abc,,US,1,Commnt1
> Abcd,,US,1,Commnt2
> Abcde,,US,1,Commnt3
> Abcdef,,US,1,Commnt4
> Abcdef,,US,1,Commnt4a
>
> I need to produce a output file that reads each record in the table and
> outputs to a file. But if the Item is the same output like(example 2).
> There could be 4 or more comments for the same Item. For each Item that is
> the same the comment line needs to end with a (~) tilde.
>
> -- Example for 1st record--
>
> @@BatchLoad pgm.a            -- This will never change
>  "abc" "" "US" "1"
>   "Commnt1"
> @@End                                 -- This won't change
>
>
>
> -- Example for record 4 & 4a--
>
> @@BatchLoad pgm.a            -- This will never change
>  "abc" "" "US" "1"
>   "Commnt4" ~
>   "Commnt4a"
> @@End                                 -- This won't change
>
>
>
> Thanks in advance for the help.
>
>
>

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:OFuYRp2jFHA.1148@TK2MSFTNGP12.phx.gbl...
> Hi
>
> You can use BCP with the QUERYOUT option or preprocess the data into a
> staging table and export from that. Using the a format file or the -t
> option can specify commas as the delimiter. To get the multiple comments
> you can self join (if you have a known fixed number of posible options) on
> the common columns something like:
>
> SELECT A.Item,A.Type,A.Lang,A.Page,A.Comment,ISNULL(B.Comment,'')
> FROM MyTable A
> LEFT JOIN MyTable B ON A.Item = B.Item AND A.Type = B.Type AND A.Lang =
> B.Lang AND A.Page = B.Page
>
> For other options try searching googlefor crosstab.
>
> http://groups-beta.google.com/groups?hl=en&lr=&q=crosstab+and+%22SQL+Server%22
>
> John
>
> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
> news:CqSdnYKLwYzFNHzfRVn-3A@kconline.com...
>>I have a SQL table defined like this.
>>
>> Item Varchar(30)
>> Type Varchar(10)
>> Lang Char(2)
>> Page Char(1)
>> Comments Varchar(75)
>>
>> Delimited data from the table
>>
>> Abc,,US,1,Commnt1
>> Abcd,,US,1,Commnt2
>> Abcde,,US,1,Commnt3
>> Abcdef,,US,1,Commnt4
>> Abcdef,,US,1,Commnt4a
>>
>> I need to produce a output file that reads each record in the table and
>> outputs to a file. But if the Item is the same output like(example 2).
>> There could be 4 or more comments for the same Item. For each Item that
>> is the same the comment line needs to end with a (~) tilde.
>>
>> -- Example for 1st record--
>>
>> @@BatchLoad pgm.a            -- This will never change
>>  "abc" "" "US" "1"
>>   "Commnt1"
>> @@End                                 -- This won't change
>>
>>
>>
>> -- Example for record 4 & 4a--
>>
>> @@BatchLoad pgm.a            -- This will never change
>>  "abc" "" "US" "1"
>>   "Commnt4" ~
>>   "Commnt4a"
>> @@End                                 -- This won't change
>>
>>
>>
>> Thanks in advance for the help.
>>
>>
>>
>
>

"Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
news:ZoednceDzeA3U3_fRVn-gw@kconline.com...
> Is there anyway to "group" this query. maybe I can go a different
> direction.
>
> WHen the Items are the same write a record like this.
>
>
> Concatenate like Items and add comments on the same line(record).
>
>
> Abcdef,,US,1,Commnt4,Commnt4a
>
>
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:OFuYRp2jFHA.1148@TK2MSFTNGP12.phx.gbl...
>> Hi
>>
>> You can use BCP with the QUERYOUT option or preprocess the data into a
>> staging table and export from that. Using the a format file or the -t
>> option can specify commas as the delimiter. To get the multiple comments
>> you can self join (if you have a known fixed number of posible options)
>> on the common columns something like:
>>
>> SELECT A.Item,A.Type,A.Lang,A.Page,A.Comment,ISNULL(B.Comment,'')
>> FROM MyTable A
>> LEFT JOIN MyTable B ON A.Item = B.Item AND A.Type = B.Type AND A.Lang =
>> B.Lang AND A.Page = B.Page
>>
>> For other options try searching googlefor crosstab.
>>
>> http://groups-beta.google.com/groups?hl=en&lr=&q=crosstab+and+%22SQL+Server%22
>>
>> John
>>
>> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
>> news:CqSdnYKLwYzFNHzfRVn-3A@kconline.com...
>>>I have a SQL table defined like this.
>>>
>>> Item Varchar(30)
>>> Type Varchar(10)
>>> Lang Char(2)
>>> Page Char(1)
>>> Comments Varchar(75)
>>>
>>> Delimited data from the table
>>>
>>> Abc,,US,1,Commnt1
>>> Abcd,,US,1,Commnt2
>>> Abcde,,US,1,Commnt3
>>> Abcdef,,US,1,Commnt4
>>> Abcdef,,US,1,Commnt4a
>>>
>>> I need to produce a output file that reads each record in the table and
>>> outputs to a file. But if the Item is the same output like(example 2).
>>> There could be 4 or more comments for the same Item. For each Item that
>>> is the same the comment line needs to end with a (~) tilde.
>>>
>>> -- Example for 1st record--
>>>
>>> @@BatchLoad pgm.a            -- This will never change
>>>  "abc" "" "US" "1"
>>>   "Commnt1"
>>> @@End                                 -- This won't change
>>>
>>>
>>>
>>> -- Example for record 4 & 4a--
>>>
>>> @@BatchLoad pgm.a            -- This will never change
>>>  "abc" "" "US" "1"
>>>   "Commnt4" ~
>>>   "Commnt4a"
>>> @@End                                 -- This won't change
>>>
>>>
>>>
>>> Thanks in advance for the help.
>>>
>>>
>>>
>>
>>
>
>

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:uVSEBQDkFHA.2904@tk2msftngp13.phx.gbl...
> Hi
>
> You really need some way of identifying comment1 against comment2 and then
> add that into a where clause, otherwise you will have to process them
> using a cursor.
>
> John
>
> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
> news:ZoednceDzeA3U3_fRVn-gw@kconline.com...
>> Is there anyway to "group" this query. maybe I can go a different
>> direction.
>>
>> WHen the Items are the same write a record like this.
>>
>>
>> Concatenate like Items and add comments on the same line(record).
>>
>>
>> Abcdef,,US,1,Commnt4,Commnt4a
>>
>>
>>
>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>> news:OFuYRp2jFHA.1148@TK2MSFTNGP12.phx.gbl...
>>> Hi
>>>
>>> You can use BCP with the QUERYOUT option or preprocess the data into a
>>> staging table and export from that. Using the a format file or the -t
>>> option can specify commas as the delimiter. To get the multiple comments
>>> you can self join (if you have a known fixed number of posible options)
>>> on the common columns something like:
>>>
>>> SELECT A.Item,A.Type,A.Lang,A.Page,A.Comment,ISNULL(B.Comment,'')
>>> FROM MyTable A
>>> LEFT JOIN MyTable B ON A.Item = B.Item AND A.Type = B.Type AND A.Lang =
>>> B.Lang AND A.Page = B.Page
>>>
>>> For other options try searching googlefor crosstab.
>>>
>>> http://groups-beta.google.com/groups?hl=en&lr=&q=crosstab+and+%22SQL+Server%22
>>>
>>> John
>>>
>>> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
>>> news:CqSdnYKLwYzFNHzfRVn-3A@kconline.com...
>>>>I have a SQL table defined like this.
>>>>
>>>> Item Varchar(30)
>>>> Type Varchar(10)
>>>> Lang Char(2)
>>>> Page Char(1)
>>>> Comments Varchar(75)
>>>>
>>>> Delimited data from the table
>>>>
>>>> Abc,,US,1,Commnt1
>>>> Abcd,,US,1,Commnt2
>>>> Abcde,,US,1,Commnt3
>>>> Abcdef,,US,1,Commnt4
>>>> Abcdef,,US,1,Commnt4a
>>>>
>>>> I need to produce a output file that reads each record in the table and
>>>> outputs to a file. But if the Item is the same output like(example 2).
>>>> There could be 4 or more comments for the same Item. For each Item that
>>>> is the same the comment line needs to end with a (~) tilde.
>>>>
>>>> -- Example for 1st record--
>>>>
>>>> @@BatchLoad pgm.a            -- This will never change
>>>>  "abc" "" "US" "1"
>>>>   "Commnt1"
>>>> @@End                                 -- This won't change
>>>>
>>>>
>>>>
>>>> -- Example for record 4 & 4a--
>>>>
>>>> @@BatchLoad pgm.a            -- This will never change
>>>>  "abc" "" "US" "1"
>>>>   "Commnt4" ~
>>>>   "Commnt4a"
>>>> @@End                                 -- This won't change
>>>>
>>>>
>>>>
>>>> Thanks in advance for the help.
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
news:d9ednaJiCeOuN37fRVn-3g@kconline.com...
> The only identifier is the first column the ITEM. When I read a new record
> that is the only way to determine if it gets appended to the existing
> record or writes a new record.
>
> Any example on how too make that work ?
>
> Thanks.
>
>
>
>
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:uVSEBQDkFHA.2904@tk2msftngp13.phx.gbl...
>> Hi
>>
>> You really need some way of identifying comment1 against comment2 and
>> then add that into a where clause, otherwise you will have to process
>> them using a cursor.
>>
>> John
>>
>> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
>> news:ZoednceDzeA3U3_fRVn-gw@kconline.com...
>>> Is there anyway to "group" this query. maybe I can go a different
>>> direction.
>>>
>>> WHen the Items are the same write a record like this.
>>>
>>>
>>> Concatenate like Items and add comments on the same line(record).
>>>
>>>
>>> Abcdef,,US,1,Commnt4,Commnt4a
>>>
>>>
>>>
>>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>>> news:OFuYRp2jFHA.1148@TK2MSFTNGP12.phx.gbl...
>>>> Hi
>>>>
>>>> You can use BCP with the QUERYOUT option or preprocess the data into a
>>>> staging table and export from that. Using the a format file or the -t
>>>> option can specify commas as the delimiter. To get the multiple
>>>> comments you can self join (if you have a known fixed number of posible
>>>> options) on the common columns something like:
>>>>
>>>> SELECT A.Item,A.Type,A.Lang,A.Page,A.Comment,ISNULL(B.Comment,'')
>>>> FROM MyTable A
>>>> LEFT JOIN MyTable B ON A.Item = B.Item AND A.Type = B.Type AND A.Lang =
>>>> B.Lang AND A.Page = B.Page
>>>>
>>>> For other options try searching googlefor crosstab.
>>>>
>>>> http://groups-beta.google.com/groups?hl=en&lr=&q=crosstab+and+%22SQL+Server%22
>>>>
>>>> John
>>>>
>>>> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
>>>> news:CqSdnYKLwYzFNHzfRVn-3A@kconline.com...
>>>>>I have a SQL table defined like this.
>>>>>
>>>>> Item Varchar(30)
>>>>> Type Varchar(10)
>>>>> Lang Char(2)
>>>>> Page Char(1)
>>>>> Comments Varchar(75)
>>>>>
>>>>> Delimited data from the table
>>>>>
>>>>> Abc,,US,1,Commnt1
>>>>> Abcd,,US,1,Commnt2
>>>>> Abcde,,US,1,Commnt3
>>>>> Abcdef,,US,1,Commnt4
>>>>> Abcdef,,US,1,Commnt4a
>>>>>
>>>>> I need to produce a output file that reads each record in the table
>>>>> and outputs to a file. But if the Item is the same output like(example
>>>>> 2). There could be 4 or more comments for the same Item. For each Item
>>>>> that is the same the comment line needs to end with a (~) tilde.
>>>>>
>>>>> -- Example for 1st record--
>>>>>
>>>>> @@BatchLoad pgm.a            -- This will never change
>>>>>  "abc" "" "US" "1"
>>>>>   "Commnt1"
>>>>> @@End                                 -- This won't change
>>>>>
>>>>>
>>>>>
>>>>> -- Example for record 4 & 4a--
>>>>>
>>>>> @@BatchLoad pgm.a            -- This will never change
>>>>>  "abc" "" "US" "1"
>>>>>   "Commnt4" ~
>>>>>   "Commnt4a"
>>>>> @@End                                 -- This won't change
>>>>>
>>>>>
>>>>>
>>>>> Thanks in advance for the help.
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:u2FZgTHkFHA.2904@tk2msftngp13.phx.gbl...
> Hi
>
> You can create the identifier yourself such as
>
> CREATE TABLE MyData (
> Item Varchar(30),
> Type Varchar(10),
> Lang Char(2),
> Page Char(1),
> Comments Varchar(75) )
>
> INSERT INTO MyData (Item, Type, Lang, Page, Comments  )
> SELECT 'Abc','','US',1,'Commnt1'
> UNION ALL SELECT 'Abcd','','US',1,'Commnt2'
> UNION ALL SELECT 'Abcde','','US',1,'Commnt3'
> UNION ALL SELECT 'Abcdef','','US',1,'Commnt4'
> UNION ALL SELECT 'Abcdef','','US',1,'Commnt4a'
>
>
>
> CREATE TABLE #Myworkingtable (
> id int not null identity(1,1),
> Item Varchar(30),
> Type Varchar(10),
> Lang Char(2),
> Page Char(1),
> Comments Varchar(75) )
>
>
> CREATE TABLE #MyOutput (
> rank int not null ,
> Item Varchar(30),
> Type Varchar(10),
> Lang Char(2),
> Page Char(1),
> Comments Varchar(75) )
>
> INSERT INTO #Myworkingtable (Item, Type, Lang, Page, Comments  )
> SELECT Item, Type, Lang, Page, Comments
> FROM MyData
> ORDER BY Item, Type, Lang, Page, Comments
>
> INSERT INTO #MyOutput (Item, Type, Lang, Page, Comments,rank  )
> SELECT W.Item, W.Type, W.Lang, W.Page, W.Comments, ( SELECT COUNT(*) FROM
> #Myworkingtable M WHERE
> W.Item = M.Item
> AND W.Type = M.Type
> AND W.Lang = M.Lang
> AND W.Page = M.Page
> AND W.ID >= M.ID )
> FROM #Myworkingtable W
>
> SELECT O1.Item, O1.Type, O1.Lang, O1.Page, O1.Comments, O2.Comments,
> O3.Comments, O4.Comments
> FROM #MyOutput O1
> LEFT JOIN #MyOutput O2 ON O1.Item = O2.Item AND O1.Type = O2.Type AND
> O1.Lang = O2.Lang AND O1.Page = O2.Page AND O2.Rank = 2
> LEFT JOIN #MyOutput O3 ON O1.Item = O3.Item AND O1.Type = O3.Type AND
> O1.Lang = O3.Lang AND O1.Page = O3.Page AND O3.Rank = 3
> LEFT JOIN #MyOutput O4 ON O1.Item = O4.Item AND O1.Type = O4.Type AND
> O1.Lang = O4.Lang AND O1.Page = O4.Page AND O4.Rank = 4
> WHERE O1.Rank = 1
>
> This can be put into a procedure that is called in your BCP command.
>
> John
>
>
>
>
>
> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
> news:d9ednaJiCeOuN37fRVn-3g@kconline.com...
>> The only identifier is the first column the ITEM. When I read a new
>> record that is the only way to determine if it gets appended to the
>> existing record or writes a new record.
>>
>> Any example on how too make that work ?
>>
>> Thanks.
>>
>>
>>
>>
>>
>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>> news:uVSEBQDkFHA.2904@tk2msftngp13.phx.gbl...
>>> Hi
>>>
>>> You really need some way of identifying comment1 against comment2 and
>>> then add that into a where clause, otherwise you will have to process
>>> them using a cursor.
>>>
>>> John
>>>
>>> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
>>> news:ZoednceDzeA3U3_fRVn-gw@kconline.com...
>>>> Is there anyway to "group" this query. maybe I can go a different
>>>> direction.
>>>>
>>>> WHen the Items are the same write a record like this.
>>>>
>>>>
>>>> Concatenate like Items and add comments on the same line(record).
>>>>
>>>>
>>>> Abcdef,,US,1,Commnt4,Commnt4a
>>>>
>>>>
>>>>
>>>> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>>>> news:OFuYRp2jFHA.1148@TK2MSFTNGP12.phx.gbl...
>>>>> Hi
>>>>>
>>>>> You can use BCP with the QUERYOUT option or preprocess the data into a
>>>>> staging table and export from that. Using the a format file or the -t
>>>>> option can specify commas as the delimiter. To get the multiple
>>>>> comments you can self join (if you have a known fixed number of
>>>>> posible options) on the common columns something like:
>>>>>
>>>>> SELECT A.Item,A.Type,A.Lang,A.Page,A.Comment,ISNULL(B.Comment,'')
>>>>> FROM MyTable A
>>>>> LEFT JOIN MyTable B ON A.Item = B.Item AND A.Type = B.Type AND A.Lang
>>>>> = B.Lang AND A.Page = B.Page
>>>>>
>>>>> For other options try searching googlefor crosstab.
>>>>>
>>>>> http://groups-beta.google.com/groups?hl=en&lr=&q=crosstab+and+%22SQL+Server%22
>>>>>
>>>>> John
>>>>>
>>>>> "Hoosbruin" <Hoosbr***@Kconline.com> wrote in message
>>>>> news:CqSdnYKLwYzFNHzfRVn-3A@kconline.com...
>>>>>>I have a SQL table defined like this.
>>>>>>
>>>>>> Item Varchar(30)
>>>>>> Type Varchar(10)
>>>>>> Lang Char(2)
>>>>>> Page Char(1)
>>>>>> Comments Varchar(75)
>>>>>>
>>>>>> Delimited data from the table
>>>>>>
>>>>>> Abc,,US,1,Commnt1
>>>>>> Abcd,,US,1,Commnt2
>>>>>> Abcde,,US,1,Commnt3
>>>>>> Abcdef,,US,1,Commnt4
>>>>>> Abcdef,,US,1,Commnt4a
>>>>>>
>>>>>> I need to produce a output file that reads each record in the table
>>>>>> and outputs to a file. But if the Item is the same output
>>>>>> like(example 2). There could be 4 or more comments for the same Item.
>>>>>> For each Item that is the same the comment line needs to end with a
>>>>>> (~) tilde.
>>>>>>
>>>>>> -- Example for 1st record--
>>>>>>
>>>>>> @@BatchLoad pgm.a            -- This will never change
>>>>>>  "abc" "" "US" "1"
>>>>>>   "Commnt1"
>>>>>> @@End                                 -- This won't change
>>>>>>
>>>>>>
>>>>>>
>>>>>> -- Example for record 4 & 4a--
>>>>>>
>>>>>> @@BatchLoad pgm.a            -- This will never change
>>>>>>  "abc" "" "US" "1"
>>>>>>   "Commnt4" ~
>>>>>>   "Commnt4a"
>>>>>> @@End                                 -- This won't change
>>>>>>
>>>>>>
>>>>>>
>>>>>> Thanks in advance for the help.
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>