Select Statement multiple columns

Transact-SQL
1

Working with a comment table that has multiple comment lines tied to an order number.

Table: oecmthst
ord_no cmt_seq_no cmt
1234 1 9/9/2015
1234 2 Tracking# 123445
1234 3 Ground

I would like to have a select statement that returned the following for each order.

ord_no Shipped Tracking # Service
1234 9/9/2015 Tracking# 123445 Ground

2

Are they always shipped = 1, tracking = 2 and service = 3?

3

I'm not 100% on that but I think so.

I was thinking of also doing left(ltrim(cmt),11) = 'Shipped on:'
I know the first part of the cmt never change.

1 Shipped on:
2. Tracking#:
3 Ground:

4

I think a unpivot might work here.

The brute force method would be

SELECT A.ord_no, A.cmt, B.cmt, C.cmt
FROM Yourtable A 
LEFT JOIN Yourtable B ON A.ord_no = B.ord_no AND B.cmt LIKE 'Tracking%'
LEFT JOIN Yourtable C ON A.ord_no = C.ord_no AND C.cmt LIKE 'Ground%'
WHERE A.cmt LIKE 'Shipped%';

You could then use STUFF or SUBSTRING to get the part of cmt you want.

1 Like
5

I went the cmt_seq_no route and that seems to be working.

SELECT real_ord_no, ltrim([1]) AS ShipDate, [2] AS Tracking, ltrim([3]) AS Service,
[4] AS Weight, [5] AS Packages, [6] as Billing
FROM
(SELECT real_ord_no, cmt_seq_no, cmt
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:')) p
PIVOT
(
max(cmt)
FOR cmt_seq_no IN
( [1], [2], [3], [4], [5], [6] )
) AS pvt

6

I would go that route if: there are relatively few JOINs, and in particular if there are "several" columns to be included from, eg., a parent table as I think that makes PIVOT unwieldy (in this case only [real_ord_no] being displayed)