0

I have a table of many key value pairs as columns in single row as below:

ID | FIELD1 | FIELD1_VAL |FIELD2 | FIELD2_VAL | FIELD3 | FIELD3_VAL
1  | REC_DT | 10 Jan     |AMD_DT | 30 Jan     | PAY_DT | 12 Jan

I want to view or store it as below:

ID | REC_DT | AMD_DT | PAY_DT
1  |10 Jan  | 30 Jan | 12 Jan

This is just an example as in the original table there are more than 100 Key value pairs.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Pallavi Singh
  • 1
  • You'll need Dynamic SQL for this. I strongly suggest fixing your design here. – Thom A Jan 18 '21 at 17:50
  • @Larnu Ok and how would that be achieved using dynamic sql? – Pallavi Singh Jan 18 '21 at 17:54
  • Is fixing the design not an option then? Like I said, I strongly recommend you do; such designs are a nightmare to work with. – Thom A Jan 18 '21 at 18:20
  • Before you go any further, identify how many different values exist in your 3 "field" columns. Don't assume by looking at some small subset of rows. Don't assume that a given value is found in Field1 only and not in Field2 or Field3. And what is "10-Jan"? Without a year it is **not** a date. Is this another forced assumption because the VAL columns are all defined as strings to allow any "datatype" to be stored? – SMor Jan 18 '21 at 18:34
  • Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – JeffRamos Jan 18 '21 at 23:31
  • The Value here can be any data type from strings to datatype hence I'm not so worried for it as I'll be storing it in varchar itself. The original table has key value pair side by side in table which is what we are getting in the file. But as an output , it needs to be stored with all keys as respective heading & storing respective values. Also for each row the number of keys can differ. eg. One row has 30 different keys and the other may be having 40 keys. – Pallavi Singh Jan 19 '21 at 03:48
  • *"The Value here can be any data type from strings to datatype hence I'm not so worried for it as I'll be storing it in varchar itself."* This is just more design flaws. [varchar is NOT a one size fits all data type](https://wp.larnu.uk/fundamentals-varchar-is-not-a-one-size-fits-all-data-type/). The more you say here, the more flawed the design becomes. You ***need*** to fix that. – Thom A Jan 19 '21 at 09:06

0 Answers0