Createing a text field in sql that is a summary

Below is the script I am using



SELECT     CONVERT(Varchar(10), BV_Item.CreationDate, 101) AS CreationDate, BV_Item.InHand, BV_ItemDetail.PONum, CONVERT(Varchar(10),
                                              BV_ItemDetail.ShipDate, 101) AS ShipDate, BV_ItemDetail.VendorName, BV_ItemDetailCombination.SKU, LEFT(BV_ItemDetailCombination.SKU, 9) AS Root,
                                              SUBSTRING(BV_ItemDetailCombination.SKU, 15, 4) AS Size, BV_ItemDetailCombination.Description, BV_ItemDetailCombination.FinalQty,
                                              BV_PO_Status.Tracking, BV_PO_Status.Notes
                       FROM          BV_Item INNER JOIN
                                              BV_ItemDetail ON BV_Item.ItemID = BV_ItemDetail.ItemID INNER JOIN
                                              BV_ItemDetailCombination ON BV_ItemDetail.ItemDetailID = BV_ItemDetailCombination.ItemDetailID LEFT OUTER JOIN
                                              BV_PO_Status ON BV_ItemDetail.PONum = BV_PO_Status.PurchaseOrder
                       WHERE      (BV_ItemDetail.Status < 40) AND (BV_Item.PGMID <> 'CUST') AND (BV_ItemDetail.ShipCompany LIKE 'BrandVia%') AND (BV_Item.Status < 50) AND
                                              (BV_ItemDetail.PONum NOT LIKE 'TBD%') AND (BV_ItemDetailCombination.LineType = 'Stocked') AND (SUBSTRING(BV_ItemDetailCombination.SKU, 15, 4)
                                              IN ('10', '12', '12M', '13IN', '14', '14IN', '15IN', '18M', '2',
                                              '2T', '3M', '4', '4T', '4XL', '6', '6M', '6T', '8', '9M', 'L/XL', 'S/M'))

The result has been attached


What I am looking for is 1 line per Root, using the Last Value of all the dates and the last Value of the description..I need to add a field that shows (as an example...line 20.. would have 'FB00-0462', all the columns listed but an extra column that shows '50@15in' .. the 50 is in the Final Qty field, and the 15IN is in the size column....

The problem I am having is that if 'FB00-0462' shows up with Mulltiple Sizes I only want to see 1 field representative of all of the Final Qtys and sizes....50@15IN, 20 @18M, 8@4XL...
eeqry.xlsx
Michael KatzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Do you want to add an additional column to the select statement, or is there a summary table or view that you are trying to create?
Michael KatzAuthor Commented:
I would like to create a view if possible with the additional column.. if that is possible
Bob LearnedCommented:
I guess what I was trying to determine is if you just need to add an additional column to that query listed in the question.  If so, then I would suggest using a function to create the summary that you need.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Michael KatzAuthor Commented:
Yes...i would need an additional column...can you assist?
Bob LearnedCommented:
Since I don't have a good understanding of your data, that will be fun, so here we go...

1) Create a function, passing in an identifier

2) In the function, select all the records that are related to the identifier passed in

3) Build a concatenated string.  There are different approaches, so you can decide which one feels appropriate

     a) Use COALESCE:

          DECLARE @Names VARCHAR(8000)
          SELECT @Names = COALESCE(@Names + ', ', '') +
              ISNULL(Name, 'N/A')
          FROM People

     b) Use XML PATH

          SELECT FName + ', ' AS 'data()'
              FROM NameList
              FOR XML PATH('')
Michael KatzAuthor Commented:
Sorry… Maybe I am too much of a rookie..  But how do I incorporate your above solution into my current script??
Bob LearnedCommented:
1) Start by creating the function (i.e.  GetItemSummary), passing in the primary key for BV_Item.

SQL Server Functions: The Basics
https://www.simple-talk.com/sql/t-sql-programming/sql-server-functions-the-basics/

2) Add an additional column that uses the function:

SELECT     CONVERT(Varchar(10), BV_Item.CreationDate, 101) AS CreationDate, BV_Item.InHand, BV_ItemDetail.PONum, CONVERT(Varchar(10),
                  BV_ItemDetail.ShipDate, 101) AS ShipDate, BV_ItemDetail.VendorName, BV_ItemDetailCombination.SKU, LEFT(BV_ItemDetailCombination.SKU, 9) AS Root,
                  SUBSTRING(BV_ItemDetailCombination.SKU, 15, 4) AS Size, BV_ItemDetailCombination.Description, BV_ItemDetailCombination.FinalQty,
                  BV_PO_Status.Tracking, BV_PO_Status.Notes, GetItemSummary(BV_Item.ItemID) AS ItemSummary
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post a sample data for BV_ItemDetailCombination table?
Michael KatzAuthor Commented:
Bob,

It appears there is more to creating a Function then what you have posted.. Yes I have gone into the link you sent , but it is not exactly straightforward in relation to your posting…Basically I am struggling with setting this up.. Not sure if you deal with idiots like me often but a bit more guidance would be most appreciated..

Thanks
Bob LearnedCommented:
I deal with inexperienced people all the time, which requires patience and persistence.  Also, I try to help you find your own answer, with guided comments.  I feel that you will remember it better that way (teach a person to fish and all that).

There are a lot of different approaches that you can take, but one approach is to open SQL Server Management Studio, and run a script like this:

Create User-defined Functions (Database Engine
https://msdn.microsoft.com/en-us/library/ms191320.aspx

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity) 
    FROM Production.ProductInventory p 
    WHERE p.ProductID = @ProductID 
        AND p.LocationID = '6';
     IF (@ret IS NULL) 
        SET @ret = 0;
    RETURN @ret;
END;
GO

Open in new window


You should see in the script, that it checks for the existence of the object, and drops it if it exists.  Then, there is the function declaration, the body, and the return statement.  You would need to change the function signature line, and function body as needed.
Michael KatzAuthor Commented:
Appreciated.. but I am stuck...cant figure this out to save my life...
Michael KatzAuthor Commented:
Help...I have spent hours coming up with nothing.. I am really desperate
Bob LearnedCommented:
Sorry, I have been working on other things.  Can you tell me what you have tried, and what problems you are having, so that I can give you a good direction to take?
Michael KatzAuthor Commented:
In one row.. I have gotten the following....the string 'FB00-0460' (which is the Root Column) is followed by size and qty...

What I need is a summary that reads
'FB00-0460' 10,48    12,48    2,60
'FB00-0459' 12M,60 3M,60  6M,60

Basically 1 row per Root with Size and Qty..

FB00-0460   10,48,  FB00-0460   12,48,  FB00-0460   2,60,  FB00-0460   4,60,  FB00-0460   6,60,  FB00-0460   8,48,  FB00-0459   12M,60,  FB00-0459   3M,60,  FB00-0459   6M,60,  FB00-0411   12M,168,  FB00-0411   3M,228,  FB00-0411   6M,228,  FB00-0462   13IN,50,  FB00-0462   15IN,50,  PP00-0185   2T,25,  PP00-0185   4T,25,  PP00-0184   10,25,  PP00-0184   6,25,  PP00-0184   8,25,  FB00-0464   L/XL,20,  FB00-0464   S/M,25,  FB00-0436   4XL,8,  FB00-0375   12M,144,  FB00-0375   3M,144,  FB00-0375   6M,144,  BB00-0111   14,1,  AB00-0979   13IN,3,  AB00-0979   14IN,6,  AB00-0979   15IN,6,  AB00-0965   13IN,6,  AB00-0965   14IN,6,  AB00-0965   15IN,6,  AB00-0582   10,5,  AB00-0582   2T,2,  AB00-0582   4T,3,  AB00-0582   6,6,  AB00-0582   8,11,  AB00-0630   12M,12,  AB00-0630   18M,12,  AB00-0630   3M,12,  AB00-0630   6M,12,  AB00-0631   18M,18,  AB00-0631   6M,18,  LI00-0118   18M,12,  LI00-0118   6M,36,  NA00-0404   4XL,6,  NA00-0406   4XL,6,  AB00-0582   10,2,  AB00-0582   2T,10,  AB00-0... I have gott
Bob LearnedCommented:
Can you show me the function that you have now?
Michael KatzAuthor Commented:
ALTER Function [dbo].[SizeSummary] ()
Returns varchar(1000)

as Begin

Declare @SummaryList varchar(1000)


SELECT    @SummaryList = Coalesce(@SummaryList + ',','') + '  '+
CAST(subroot as varchar(15)) + '   '+
Cast(Size as varchar (15))+','+
CAST(FinalQty as varchar(15)) from
Test

Return @SummaryList

end


Test is the name of a view
Michael KatzAuthor Commented:
I have other columns that need to be in the qry...not sure if you need those
Bob LearnedCommented:
I would think that you need a WHERE clause to limit the output, so you would need a parameter for the ID (or primary key column), so that you could correlate the summary output.
Michael KatzAuthor Commented:
Absolutely have no clue how to proceed…Please help…
Bob LearnedCommented:
You never did say what the identity column is, so here is a guess:

ALTER Function [dbo].[SizeSummary] (@ItemId int)

...

SELECT    @SummaryList = Coalesce(@SummaryList + ',','') + '  '+
                CAST(subroot AS varchar(15)) + '   '+
               CAST(Size AS varchar (15))+','+
               CAST(FinalQty AS varchar(15))
      FROM Test
      WHERE ItemId = @ItemId
Michael KatzAuthor Commented:
Because I don't know how to find the identity column… You are talking so far above me…It is really unfortunate that I cannot do this work…. I have spent nearly 40 hours and gotten nowhere…literally a complete waste of time..

I realize the idea behind this is to learn and educate myself …But know i just look foolish trying and getting nowhere..I really don't know how to answer your questions….
Bob LearnedCommented:
I will continue to find a way to help you, so that you can help me, so that I can help you with the ultimate solution.

Here are some steps that you can use with SQL Server Management Studio to find the primary key column for a table:

1) Open SQL Server Management Studio (install if necessary)

2) Connect to the database server where the database is located

3) Expand the tree, until you see the tables for the database that you need (I am using the Employee table from the AdventureWorks2014 for this example).

SQL Server Management Studio - Tables
4) Expand the table to see the Columns node

5) Expand the Columns node

SQL Server Management Studio - Table Columns
   The Columns shows the following information
       a) Column name
       b) PK = Primary Key, which uniquely identifies the record, and should be required for good database design.
       c) FK = Foreign Key, which is used in relationships with other tables (the primary key in another table) that are used for join conditions.
       d) Data type
       e) Does column allow null values
       f) String column data size (varchar(256) = 256 character limit)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.