SQLTeam.com | Weblogs | Forums

Problem with Select Statement


Have a oecmthst_sql table which holds comments. Each Ord_no has a different Comment.

Tried this script to strip out the info I need to put into one record.

When there is more than one Ord_no for the same real_ord_no it only pulls from the first ord_no record.

How can Make it grab the correct comments to match the ord_no it belongs to?


First if you post your code instead of a image of the code it helps people help you.

I would suggest using something like: SELECT A.ord_no, SD.cmt AS ShipDate, t.cmt AS Tracking, ser.cmt AS Service FROM Oemthst_sql A LEFT JOIN Oemthst_sql SD ON A.Ord_no = SD.ord_no AND SD.cmt like 'Shipped on%' LEFT JOIN Oemthst_sql T ON A.Ord_no = T.ord_no AND T.cmt like 'Tracking%' LEFT JOIN Oemthst_sql SER ON A.Ord_no = SER.ord_no AND SER.cmt like 'Service%';
Note I only did a few to give you the idea.


SELECT ord_no, ltrim([1]) AS ShipDate, [2] AS Tracking, ltrim([3]) AS Service, [4] AS Weight, [5] AS Packages, [6] AS Billing, [7] AS LTL, real_ord_no
FROM (SELECT ord_no, cmt_seq_no, cmt, real_ord_no
FROM oecmthst_sql
WHERE line_seq_no = 0 AND (LEFT(ltrim(cmt), 11) = 'Shipped on:' OR
LEFT(ltrim(cmt), 10) = 'Tracking#:' OR
LEFT(ltrim(cmt), 8) = 'Service:' OR
LEFT(ltrim(cmt), 13) = 'Total Weight:' OR
LEFT(ltrim(cmt), 19) = 'Number of Packages:' OR
LEFT(ltrim(cmt), 15) = 'Billing Option:' OR
LEFT(ltrim(cmt), 3) = 'LTL')) p PIVOT (max(cmt) FOR cmt_seq_no IN ([1], [2], [3], [4], [5], [6], [7])) AS pvt


A picture of table structure and/or data is useless. I don't have time to transcribe all the column names and data, so I can't fully code it out.

But in general you'll want to do a cross-tab, like this:

    MAX(CASE WHEN LTRIM(cmt) LIKE 'Shipped on:%' THEN LTRIM(cmt) END) AS ShipDate,
    MAX(CASE WHEN LTRIM(cmt) LIKE 'Tracking#:%' THEN LTRIM(cmt) END) AS Tracking,
    MAX(CASE WHEN LTRIM(cmt) LIKE 'Service:%' THEN LTRIM(cmt) END) AS Service,
FROM oecmthst_sql
WHERE line_seq_no = 0
GROUP BY ord_no
ORDER BY ord_no


Its easier than typing in what the data looks like in the table.

I'm not aware of a better way to handle it. Suggestions are welcome.


Post create table statements and insert statements so someone can copy it and paste it to an SSMS window and run it. Do that for the source tables and also create a table with for the output data you want to see. For example:

CREATE TABLE #tmpSource(
	order_type CHAR(1),
	ord_no INT,
	line_seq_no INT
INSERT INTO #tmpSource
        ( order_type, ord_no, line_seq_no )
VALUES  ( 'O',162100,0),
		( 'O',162100,1),
		( 'O',162100,2);

You don't have to post all the columns in the table, or all the data in the table. Simplify it so there are only the required columns and representative sample data.

You can also generate scripts using SSMS. If you right click on the database name in SSMS object explorer and select generate scripts, it allows you to choose one or more tables, and using advanced options generate scripts to insert data as well. But if your table has a lot of columns and lot of data, that is going to be a lot of script, which people would be hesitant to spend the time to read and respond to.

This article might be useful.