Reformat Data

I have a problem in that I need to reformat my data.

I have the data for part numbers to products from the selection.
e.g.

Part No Product Application
001 aaaa Standard
001 bbbb Option
002 aaaa Option
002 bbbb Standard
003 aaaa Option

and I wish reformat it into

Part No aaaa bbbb
001 Standard Option
002 Option Standard
003 Option Not App (Note missing from above selection)

There could be several products with various parts applicable / not applicableThis may be a little crude, and I'm sure that someone can point out better ways to do it. However it does work with the limited example that you describe.

The first part is just me building a temptable to select from

Hope it helps,

Brent

Create Table #TempParts (PartNo int, parttype varchar(5), description varchar(10))
insert #tempparts values(001, 'aaaa', 'standard')
insert #tempparts values(001, 'bbbb', 'option')
insert #tempparts values(002, 'bbbb', 'standard')
insert #tempparts values(002, 'aaaa', 'option')
insert #tempparts values(003, 'aaaa', 'option')

select PartNo,
Max(Case parttype when 'aaaa' then [description] end) as AAAA,
Max(Case parttype when 'bbbb' then [description] end) as BBBB
from #tempparts
group by partno|||Thanks for your reply but the problem is when I do the query on part no's I don't know at that time to what selection of Products they are applicable, and unfortunately there are hundreds of possible products.|||Do you have an equally large amount of Part No's as Products? What about Applications? Just trying to get an idea of the variables that need to be arranged in some sort of pivot table.

reference numbers whose status is not 2 (was "SQL Newbie looking for a little he

Hi All,

Not exactly a SQL programmer by a long way and I'm having a problem with a query and would like a little advice if someone would be so kind.

I have a table with various fields, of which I'm interested in two (RefNumber and Status).

The RefNumber is not unique has it can be linked to multiple users at the same time. So the data I have looks like this (for the two fields in question).

RefNumber / Status
1234 / 1
1234 / 1
1234 / 2
3333 / 1
3333 / 1
3333 / 1
5555 / 2
5555 / 1

What I'm trying to get out of the database is distinct reference numbers whose status is not at 2.

So the result I'm after int this case would be "3333".

I've tried the following query, but it doesn't seem to work.

select ReferenceNumber from CountTable c where Status<
(SELECT max(status) as Status
FROM CountTable cl
where cl.ReferenceNumber = c.referencenumber
group by referencenumber)

Any pointers greatly appreciated.Your nested query just needs to find those refnumbers that have a status of 2, and your outer query finds the rest

SELECT DISTINCT REFNUMBER
FROM COUNTTABLE
WHERE NOT REFNUMBER IN (
SELECT DISTINCT REFNUMBER
FROM COUNTTABLE
WHERE STATUS=2)|||select ReferenceNumber
from CountTable
group
by ReferenceNumber
having sum(
case when status = 2
then 937
else 0 end ) = 0|||Rudy,

I'll concede that you're much better at this than I am, so I'll assume
that your's is the better solution. My question is "Why"?

Mark|||Thanks guys - both those queries work perfectly. :beer:

I must admit, I have little more idea of what RedNeckGeek's is doing over r937's query.

I'll have a read up on the unknown (to me) functions you use.

Don't think I'll ever fully understand SQL, which is quite sad as it seems quite powerful.

I'm sure, when I do things in code to manipulate data read from a DB, I could do them in SQL with much less overhead. At the moment, I used stored procedures in SQLServer to get data, manipulate it in code then update the DB again through stored procs......I can almost hear your screams of pain from here! :eek:

Any pointers to some good idiot-proof tutorials??

Thanks again for your help.

Regards,

Mark J.|||Or still:SELECT DISTINCT RefNumber
FROM CountTable AS c
WHERE NOT EXISTS (
SELECT 1
FROM CountTable
WHERE RefNumber = c.RefNumber AND Status = 2)This might be more performant than the others (depending on the size of the table, the average number of entries per RefNumber, and the RDBMS).|||You actually seem to have a pretty good deal working already. You understand that SQL is powerful, but like any powerful tool it requires some basic knowledge of the tool and/or instruction before you can use it safely. You've got somebody managing your SQL Server that understands your limits and is providing you with stored procedures to do the "heavy lifting" on the SQL side. This is definitely not a bad place to start!

I'm not sure what kind of resources you are looking for (web sites, classes, books, etc). I'll give you a couple of my suggestions, but feel free to ask more or different questions.

First and foremost, asking questions here is likely to be a really good source of information for you. There are a bunch of crusty old pharts like me, and a few folks that actually present a professional image too. We all have opinions, and most of those opinions are pretty good, and lord knows if you ask a question you're pretty likely to get some good answers.

Don't overlook the possibility of classes at a local university, trade school, or even an Adult Education class. The hands on nature and the interaction with people is really valuable to most people in the early stages of the learning process for almost anything. The quality varies all over the map, running from astounding (in a good sense) to astounding (as in a waste of time) depending on the class, instructor, and student... While this can definitely be a "mixed bag" and can go wrong, it usually goes very, VERY right.

If you are looking for books, then I'd recommend SQL Queries for Mere Mortals (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&EAN=9780201433364&itm=1) or the Hitchhiker's Guide (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&EAN=9780321243621&itm=1) for someone using Microsoft SQL Server.

There are literally thousands of other resources. Give me a nudge in the right direction and I bet I can suggest something!

-PatP

Reduce string to binary comparison

Is it possible to create a binary comparison from a string? For numbers, you can use this syntax:

Value * (1-ABS(SIGN([Column Name] - [Desired Value))) which will evaluate to one or zero.

For strings, I thought it would be easier. I tried:

Value * ([Column Name]=[Desired Value])

But that does not work. I get an "incorrect syntax near = error. I am looking for a solution that does not rely on the case statement.

Thanks,

AndrewComparison operators return a Boolean data type - TRUE, FALSE, UNKNOWN. As such, you cannot perform arithmetic on them. You are stuck with a CASE statement, or you could use a UDF to do the comparison and return a 1 or 0.

Terri|||Thanks for your response.

Andrew