0

I have a Table of Items which has some columns [Id], [ItemOrdinal], [Amount], [Qty]

[ItemOrdinal] is an integer that basically represents an embedded Id from the imported file. This table is data translated from a file which has fields like Item1Amt.
The problem I have is a legacy system will need to access the data as if it is looking at the old system.

So I would like to be able to return a view - I have been reading about CTE's and Pivot tables, my thought is more in line with a CTE where basically I create the columns in the view and insert into them

How can I get a result set with columns named like this Item[ItemOrdinal]Amt, Item[ItemOrdinal]Qty preferably in a View typically the max number of rows will be 8 rows?

Item1Amt, Item1Qty, Item2Amt, Item2Qty, ...Item8Amt, Item8Qty

I am looking at the answer here^

DB Fiddle Example of the tables Table 2 of the examples in particular

Ken
  • 2,518
  • 2
  • 27
  • 35
  • I don't follow what you're after here. What have you tried, and why didn't it work? You talk about dynamic columns, but nothing in SQL supports this unless it uses dynamic SQL and a `VIEW` cannot use dynamic SQL, neither can functions. You're limited to stored procedures, which likely aren't what you want. Without sample data and expected results and a clear goal, however, this is vague at best. What have you tried as well? – Thom A Feb 03 '20 at 07:58
  • @Lamu I block quoted what I am after. I want columns named with an OrdinalNumber and the column name - so I get 7 rows back - I want it flattened out into 1 row - Identified by the ordinal number. – Ken Feb 03 '20 at 08:58
  • That's what you're after, but what's your question? What is it you've tried that isn't working? You haven't asked anything here (there isn't a single question mark (`?`) in your "question"). Without a clear problem, and a question, how can we answer? Sample data and Expected results *need* to be included in your question, not some off site link. You use fiddles to compliment your answer, but they shouldn't be required to consume your question. – Thom A Feb 03 '20 at 09:04

1 Answers1

1

You can left-join with all 8 item tables and present them without a pivot.

Assuming that, the base table is MyBaseTable and you another 8 tables Item1 ... Item8.

Select
 [Id],
 [ItemOrdinal],
 [Amount],
 [Qty],

 Item1.Amt as Item1Amt,
 Item1.Qty as Item1Qty,

 Item2.Amt as Item2Amt,
 Item2.Qty as Item2Qty,

 Item3.Amt as Item3Amt,
 Item3.Qty as Item3Qty,

 Item4.Amt as Item4Amt,
 Item4.Qty as Item4Qty,

 Item5.Amt as Item5Amt,
 Item5.Qty as Item5Qty,

 Item6.Amt as Item6Amt,
 Item6.Qty as Item6Qty,

 Item7.Amt as Item7Amt,
 Item7.Qty as Item7Qty,

 Item8.Amt as Item8Amt,
 Item8.Qty as Item8Qty

From MyBaseTable as Base
Left Join Item1 on Item1.[ItemOrdinal] = Base.[ItemOrdinal]
Left Join Item2 on Item2.[ItemOrdinal] = Base.[ItemOrdinal]
Left Join Item3 on Item3.[ItemOrdinal] = Base.[ItemOrdinal]
Left Join Item4 on Item4.[ItemOrdinal] = Base.[ItemOrdinal]
Left Join Item5 on Item5.[ItemOrdinal] = Base.[ItemOrdinal]
Left Join Item6 on Item6.[ItemOrdinal] = Base.[ItemOrdinal]
Left Join Item7 on Item7.[ItemOrdinal] = Base.[ItemOrdinal]
Left Join Item8 on Item8.[ItemOrdinal] = Base.[ItemOrdinal]

New answer based on new fiddle SQL Fiddle:

Select
 Base.[Id],
 Base.[ItemOrdinal],
 Base.[ItemDesc],
 Base.[ItemAmt],
 Base.[ItemQty],

 Item3.[ItemAmt] as Item3Amt,
 Item3.[ItemQty] as Item3Qty,

 Item4.[ItemAmt] as Item4Amt,
 Item4.[ItemQty] as Item4Qty

From [dbo].[Tbl_2] as Base
Left Join Tbl_3 as Item3 on Item3.[ItemOrdinal] = Base.[ItemOrdinal]
Left Join Tbl_4 as Item4 on Item4.[ItemOrdinal] = Base.[ItemOrdinal]
XAMT
  • 1,515
  • 2
  • 11
  • 31
  • I added a db fiddle link - table 2 is the table of concern. I am getting a Item1.### of the multipart identifier can not be bound. – Ken Feb 03 '20 at 08:37
  • That many joins, to the same table, isn't going to be great for performance. – Thom A Feb 03 '20 at 08:38
  • @Lamu I am not sure any method to do this will be very performant, however doing it is what I need to do. – Ken Feb 03 '20 at 08:45
  • @Ken; please provide the schema of tables and i will fiddle it. – XAMT Feb 03 '20 at 08:59
  • @XAMT its in the dbfiddle link I provided in my post - does the link work for you ? see table 2 in the schema. The FKtable_Id is the aggregate group – Ken Feb 03 '20 at 09:01
  • @XAMT the only table of interest was table 2, I made this work by using SELECT TOP(1) INNERJOIN SELECT (columns) FROM tbl2 Where ItemOrdinal =1 ..7) On tblId = base.Id .. I marked as answer ..as it gives me what I need. – Ken Feb 03 '20 at 16:04
  • @Ken; That's nice. So, don't forget to use the `Order By` statement. – XAMT Feb 03 '20 at 16:29