-2

basically I'm trying to merge two tables. But the output seems quite different from a NORMAL output created by JOIN. This may sound dumb but since I don't know how to elaborate it, I prepared a snapshot. (click the link below)

desired output

The difference, from a normal JOIN statement is that, the multiple records of table "REAGENT_LOTS_ADDITIONALINFO" is merged into a single record of table REAGENT_LOTS.

In advance, I owe you people. Thank you.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
Jimin Choi
  • 7
  • 4
  • Join has given you correct output of how join works thats correcf but you want different sort of output via join you need modification as your rows are being converted to columns – Himanshu Nov 17 '18 at 07:48
  • 1
    You need to look into `PIVOT` – Nick Nov 17 '18 at 07:58
  • 1
    Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – Himanshu Nov 17 '18 at 08:13
  • Hi. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained. – philipxy Nov 17 '18 at 16:39
  • @MatBailie Please do not inline links inappropriately. See my comment above re using text whenever possible. Either replace a link to a table by code block formatted tabular text (which should simultaneously be code to create & initialize a table if the question should give a [mcve]) or, much better, comment to tell the poster to do it. – philipxy Nov 17 '18 at 16:41
  • @philipxy - That's your opinion. I disagree. – MatBailie Nov 17 '18 at 16:44
  • @philipxy hmmm this is confusing maybe I should use both text & image? I thought image would help people understand the problem. – Jimin Choi Nov 18 '18 at 05:35
  • What is confusing? Did you read the link re why text? Just use text for tables, which should also be initialization code if you are giving a [mcve]. – philipxy Nov 18 '18 at 07:25
  • @MatBailie Coincidental [current "hot meta post" "asked yesterday"](https://meta.stackoverflow.com/questions/376751/raise-the-amount-of-reputation-needed-to-stop-seeing-the-warning-on-the-image-up). Which links to [this](https://meta.stackoverflow.com/questions/361474/should-we-display-a-warning-when-users-include-images), among others. – philipxy Nov 18 '18 at 12:33

1 Answers1

1

Follow this pattern:

SELECT
  lot.reagentlotid,
  lot.reagentlotdesc,
  MAX(CASE WHEN inf.attributeid = 'rl_a01' THEN inf.textvalue END) as rl_a01,
  MAX(CASE WHEN inf.attributeid = 'rl_a02' THEN inf.textvalue END) as rl_a02,
  ... --add more "max case when" here

FROM
  Reagent_lots lot
INNER JOIN
  reagent_lots_additionalinfo inf
    ON inf.reagentlotid = lot.reagentlotid
GROUP BY 
    lot.reagentlotid,
    lot.reagentlotdesc

Where the ... is, add as many repetitions of the MAX CASE WHEN as necessary to cover the conversion of attribute rows to columns

Sqlserver does have a proprietary PIVOT syntax, but I tend to do pivots in this pattern because it works across every database vendor

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • what if the rows are more for acting as columns if suppose 1000 i need to add manually case when 1 end case when 2 end..case when 1000 end – Himanshu Nov 17 '18 at 08:34
  • 1
    It's a basic tenet of database implementation across every vendor(and even database theory in general), that the rows are infinitely variable in number and the columns are fixed/limited in number. Columns represent different attributes of a data item. Rows represent different data items. You'd hence have to put 1000 max case when statements to represent your 1000 columns and the same is true of any PIVOT method/any query. Use rows for what they are designed, and columns for what they are designed. Switching it round is **doing it wrong**. *"Just because you can, doesn't mean you should"* – Caius Jard Nov 17 '18 at 09:33
  • exactly this is what i meant should use rows for values and columns for attributes but in case we have pivot your approach looks of no use in case theres more rows pivot is the best option perhaps some other database would have certain pivot like functions that might be handy in use. I have never seen this type of use treating rows as columns at most. – Himanshu Nov 17 '18 at 09:39
  • You don't seem to have read the question, or understand what a pivot is – Caius Jard Nov 17 '18 at 09:58
  • I said rather writing each time case when end... case for each row as column use pivot (column for col1 in (dynamic_pivot)) meaning just creating a dynamic_pivot as total no of rows then pivoting to create columns likewise. Is it a 1000 line case statement for 1000 rows if exists. I hope this makes it clear what I said. – Himanshu Nov 17 '18 at 10:22
  • @himanshuahuja You might not have seen this before, but it is a standard pattern for flexibly pivoting data. There certainly ARE cases when this tabular format is preferred to a normalised format. Trying to fit the world in to inflexible categorical rules is a fruitless and pointless endeavour. – MatBailie Nov 17 '18 at 10:26
  • @MatBailie I have seen this is the standard I agree the data is normalised first and converted to opposite. In terms of flexibility, I will have to agree but in terms of time for manually adding Idk perhaps I will have to agree with you both great learning guys thanks :) – Himanshu Nov 17 '18 at 10:35
  • 1
    Pivoting should really be done by the front end application (eg some reports package) not the DB.. this is, however, one of the typical ways (using standard SQL common to most major DB) of having the database do it. JiminChoi doesn't store his data like this, he seeks a way to rearrange it for presentation - that's essentially the right way to do it, even if it should perhaps not be the DB doing the rearranging – Caius Jard Nov 17 '18 at 12:29
  • Hi guys, sorry for late catch up. First of all, @CaiusJard you're SQL works like a charm thanks. And like HimanshuAhuja i'm not used to this kind of pattern but thanks MatBailie and CaiusJard for letting me know that this IS pretty much the standard. You guys rock – Jimin Choi Nov 17 '18 at 12:34