Problem With CASE
-
Hello,
I have a table called WellsResubmit_Staging that contains a single column called ‘ResubmitDetails’. A sample of three records looks like this;
Seller UserId: NO MATCHING DISBURSEMENT!
Business Name: NO MATCHING DISBURSEMENT!
Order ID: 166118
I need to reorient this data so that the text to the left of the colon becomes the column names, and the text to the right of the colon becomes the data in the column.
I’ve attempted to do that in the following way;
SELECT
CASE
WHEN LEFT(ResubmitDetails,CHARINDEX(':',ResubmitDetails,1)) = 'Seller UserID:'
THEN REVERSE(LEFT(REVERSE(ResubmitDetails),(CHARINDEX(':',REVERSE(ResubmitDetails),1)-2)))
ELSE '' END AS SellerUserID,
CASE
WHEN LEFT(ResubmitDetails,CHARINDEX(':',ResubmitDetails,1)) = 'Business Name:'
THEN REVERSE(LEFT(REVERSE(ResubmitDetails),(CHARINDEX(':',REVERSE(ResubmitDetails),1)-2)))
ELSE '' END AS BusinessName,
CASE
WHEN LEFT(ResubmitDetails,CHARINDEX(':',ResubmitDetails,1)) = 'Order ID:'
THEN REVERSE(LEFT(REVERSE(ResubmitDetails),(CHARINDEX(':',REVERSE(ResubmitDetails),1)-2)))
ELSE '' END AS OrderID
FROM WellsResubmit_Staging
WHERE NOT CHARINDEX(':',ResubmitDetails,1)= 0
In the results, the data in each column appears in its own row like this;
SellerUserID BusinessName OrderID
C419304000010003
EOM-CPS TEST QSR
440712
In order for this data to appear in a single row, do I need to incorporate MAX in some way, or is there some other problem?
Thank you for your help!
CSDunn
-
SELECT MAX(
CASE
WHEN ResubmitDetails LIKE 'Seller UserID:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 15, 8000)))
ELSE ''
END
 
AS SellerUserID,MAX(
CASE
WHEN ResubmitDetails LIKE 'Business Name:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 15, 8000)))
ELSE ''
END
 
AS BusinessName,MAX(
CASE
WHEN ResubmitDetails LIKE 'Order ID:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 10, 8000)))
ELSE ''
END
 
AS OrderIDFROM WellsResubmit_Staging
WHERE ResubmitDetails LIKE '%:%'
N 56°04'39.16"
E 12°55'05.25" -
Thanks. I tried this, and I only got one record back (the MAX record). Below is a larger sample of the data from WellsResubmit_Staging;
Seller UserId: C544068076250756
Business Name: EOM-PHILLY CONNECTIONS
Order ID: 395989
Seller UserId: C544068076250885
Business Name: EOM-CITY KITCHEN
Order ID: 396024
Seller UserId: C544068076250945
Business Name: EOM-FRASSO INVESTMENTS
Order ID: 396050
This data needs to be reoriented so that it looks like this;
Seller UserID Business Name OrderID
C544068076250756 EOM-PHILLY CONNECTIONS 396050
C544068076250885 EOM-CITY KITCHEN 396024
C544068076250945 EOM-FRASSO INVESTMENTS 396050
-
-- Stage the data
SELECT CASE
WHEN ResubmitDetails LIKE 'Seller UserID:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 15, 8000)))
ELSE ''
END AS SellerUserID,
CASE
WHEN ResubmitDetails LIKE 'Business Name:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 15, 8000)))
ELSE ''
END AS BusinessName,
CASE
WHEN ResubmitDetails LIKE 'Order ID:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 10, 8000)))
ELSE ''
END AS OrderID,
IDENTITY(INT, 0, 1) AS RecID
INTO #Temp
FROM WellsResubmit_Staging
WHERE ResubmitDetails LIKE '%:%'
-- Show the data
SELECT MAX(SellerUserID),
MAX(BusinessName),
MAX(OrderID)
FROM #Temp
GROUP BY RecID / 3
ORDER BY RecID / 3
N 56°04'39.16"
E 12°55'05.25" -
If your table WellsResubmit_Staging already has an IDENTITY column,
SELECT
MAX(SellerUserID),
MAX(BusinessName),
MAX(OrderID)
FROM (
SELECT CASE
WHEN ResubmitDetails LIKE 'Seller UserID:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 15, 8000)))
ELSE ''
END AS SellerUserID,
CASE
WHEN ResubmitDetails LIKE 'Business Name:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 15, 8000)))
ELSE ''
END AS BusinessName,
CASE
WHEN ResubmitDetails LIKE 'Order ID:%' THEN RTRIM(LTRIM(SUBSTRING(ResubmitDetails, 10, 8000)))
ELSE ''
END AS OrderID,
ROW_NUMBER() OVER (ORDER BY <SomeIdentityCol>
- 1 AS RecIDFROM WellsResubmit_Staging
WHERE ResubmitDetails LIKE '%:%'
) AS d
GROUP BY RecID / 3
ORDER BY RecID / 3
N 56°04'39.16"
E 12°55'05.25" -
Thanks again. The records are still 'staggard' in the table, and there are rows between the staggard records where every column is an empty string. I'm wondering if the encoding of the file has something to do with it. The encoding of the file is UTF-8. It took a couple of times to get the data to import correctly. It appeared that bringing the file in as a tab delimited file would work. I also tried brining the file in as comma delimited. The results I got back between the two import configurations were very different.
A sample of the current result looks like this after I put the results into a text file;
SellerUserID BusinessName OrderID
NO MATCHING DISBURSEMENT! NO MATCHING DISBURSEMENT! 166118
NO MATCHING DISBURSEMENT!
NO MATCHING DISBURSEMENT! 167996RRRRR
NO MATCHING DISBURSEMENT! NO MATCHING DISBURSEMENT!
169935
-
Just had a quick play with this, here's the content of a sample text file:
Seller UserId: NO MATCHING DISBURSEMENT!
Business Name: NO MATCHING DISBURSEMENT!
Order ID: 166118
Seller UserId: C544068076250756
Business Name: EOM-PHILLY CONNECTIONS
Order ID: 395989
Seller UserId: C544068076250885
Business Name: EOM-CITY KITCHEN
Order ID: 396024
Seller UserId: C544068076250945
Business Name: EOM-FRASSO INVESTMENTS
Order ID: 396050
I used SQL import from EM with the text file driver, with : as delimiter, to generate two columns in a new SQL Server table as follows (output as text from QA):
Col001,Col002
Seller UserId, NO MATCHING DISBURSEMENT!
Business Name, NO MATCHING DISBURSEMENT!
Order ID, 166118
Seller UserId, C544068076250756
Business Name, EOM-PHILLY CONNECTIONS
Order ID, 395989
Seller UserId, C544068076250885
Business Name, EOM-CITY KITCHEN
Order ID, 396024
Seller UserId, C544068076250945
Business Name, EOM-FRASSO INVESTMENTS
Order ID, 396050
Now, this could easily be processed into a three-column table as shown previously in this thread, however as you point out upstairs <<there are rows between the staggard records where every column is an empty string>> - does this mean that there are not necessarily three rows per "record"?
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
First create a temp table
CREATE TABLE #temp ([ID] int ,colname varchar(255),colvalue varchar(255))
and fill it
INSERT INTO #temp select 0,LEFT(ResubmitDetails,CHARINDEX(':',ResubmitDetails)),LTRIM(SUBSTRING(ResubmitDetails,CHARINDEX(':',ResubmitDetails)+1,255)) from
Assign an ID to each block of data for each occurance of 'Order ID'
DECLARE @ID int
SET @ID = 1
UPDATE #temp
SET [ID] = CASE WHEN colname = 'Order ID:' THEN @ID - 1 ELSE @ID END,
@ID = CASE WHEN colname = 'Order ID:' THEN @ID + 1 ELSE @ID END
Then put the data together
SELECT a.colvalue AS [Order ID],COALESCE(b.colvalue,'') AS [Seller UserId],COALESCE(c.colvalue,'') AS [Business Name]
FROM #temp a
LEFT JOIN #temp b ON b.[ID] = a.[ID] AND b.colname = 'Seller UserId:'
LEFT JOIN #temp c ON c.[ID] = a.[ID] AND c.colname = 'Business Name:'
WHERE a.colname = 'Order ID:'
It would help if the ID was assigned when the data was loaded and would guarantee the order of the data
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks again for the helpful responses. I'll dig into this, and come back to the post shortly.
CSDunn
-
David, this is great! I have other columns that I need to work into this, but I don't anticipate any problems.
Thanks again to all for your assistance!
CSDunn
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply