Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Syntax

Posted on 2009-02-20
8
Medium Priority
?
182 Views
Last Modified: 2013-11-10
Heres my data..It comes it sets of 2 and always has the Codes in the first record and the QTY in the second record...I need to update a 3rd column(QTY)With the second rows 2nd column...
00000041517      0120;0250;0250;
00000041517      1;2;1;
00001337245      0099;0099;
00001337245      1;-1;

This is how I need the data to look
Accountid          Codes                       QTY
00000041517      0120;0250;0250;     1;2;1;

I tried an Update but was unsuccessful. I was close though ;) Please help!!
0
Comment
Question by:healthcheckinc
  • 4
  • 3
8 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 23693237
Hello healthcheckinc,

Is there another column on your table that indicates which of the pair is firt and which is 2nd?  If not, how
are we to know?

Regards,

Patrick
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 23693469
healthcheckinc,

Check out the script below.  You can ignore the first part of it as that was just to create a temp table for me to hold your data for testing purposes.

Note, I had to make a few assumptions since there was no other column (at least that you provided) that differentiated the two values:

1. There will always be 2 records for each AccountID.  One that contains the Codes value and another that contains the QTY value.
2. There will always be at least one code in the list of code values. (i.e. 1234;;; or 1234;3456;; or 1234;3456;5678;)
3. Each cove value will be 4 characters, and all quantities will be less than 4 characters.


CREATE TABLE #a (AccountID VARCHAR(50), TXT VARCHAR(50))
 
INSERT INTO #a VALUES ('00000041517','0120;0250;0250;')
INSERT INTO #a VALUES ('00000041517','1;2;1;')
INSERT INTO #a VALUES ('00001337245','0099;0099;')
INSERT INTO #a VALUES ('00001337245','1;-1;')
 
WITH CTE_Accounts (AccountID, ColType, TXT) AS
(
  SELECT AccountID
       , CASE CHARINDEX(';',TXT)
           WHEN 5 THEN 'Codes'
           ELSE 'QTY'
         END
       , TXT
    FROM #a
)
SELECT AccountID
     , TXT AS Codes
     , (SELECT TXT FROM CTE_Accounts b WHERE a.AccountID = b.AccountID AND b.ColType = 'QTY') AS Qty
  FROM CTE_Accounts a
  WHERE ColType = 'Codes'

Open in new window

0
 

Author Comment

by:healthcheckinc
ID: 23694040
AaronAkin,
I would love to use this code but I am trying to configure it to work in my scenario. With that said, what would I need to change to work with my table and columns.
TableName = GlendaleRev
Columns = PatAccount, Codes, QTY

Thanks for your help. I think this CTE functionality is awesome. It is very fast. I have to learn how to use it myself. Thanks for your help...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:aaronakin
ID: 23694621
healthcheckinc,

No problem, I can change it to work for your table.  You gave me 3 columns, but in your original post, you are only showing 2 columns.  What are those column names?
0
 

Author Comment

by:healthcheckinc
ID: 23695205
These columns are the result of a SSIS data flow task...The second column holds both the Codes and QTY fields but in the same column. So I added a 3rd column QTY and want to update the 3rd column from the 2nd column with the QTY info. So the only columns that have data are PatAccount and Codes. Is that what you were looking for? Sorry if Im not making sense...
0
 
LVL 11

Accepted Solution

by:
aaronakin earned 2000 total points
ID: 23695227
Yep, makes sense.  Try this.
WITH CTE_GlendaleRev (PatAccount, ColType, Codes) AS
(
  SELECT PatAccount
       , CASE CHARINDEX(';',Codes)
           WHEN 5 THEN 'Codes'
           ELSE 'QTY'
         END
       , Codes
    FROM GlendaleRev
)
SELECT PatAccount
     , Codes
     , (SELECT Codes FROM CTE_GlendaleRev b WHERE a.PatAccount = b.PatAccount AND b.ColType = 'QTY') AS Qty
  FROM CTE_GlendaleRev a
  WHERE ColType = 'Codes'

Open in new window

0
 

Author Comment

by:healthcheckinc
ID: 23695265
Aaron your a stud...Now I have to put all the into a different format but you definately helped me out. Please look for my posts in the future, I love using the CTE functionality. Thanks again
0
 

Author Closing Comment

by:healthcheckinc
ID: 31549280
Awesome job, Thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question