Need Help with SQLServer 2008R2 View

Trying to create a view with all the values of a field for the same ID into one text field.

I know how to do this in vb but need to know how to do something like this in a SqlServer view.

basically i want to return 1 record with all the information from a field spanning multiple records that have the same criteria.

this is the vb code that pulls the data into 1 variable called cTrackingInfo.

   SSQL2 = "SELECT TRACKING_INFO FROM CT_PACKLIST_TRACKING_INFO WHERE PACKLIST_ID = '" & rs("PACKLIST_ID") & "' ORDER BY ID"
   SET RS2 = CREATEOBJECT("ADODB.RECORDSET")
   RS2.OPEN SSQL2, SCONN
   Do Until RS2.eof
      If RS2("TRACKING_INFO") <> "" Then
         If Trim(cTrackingInfo & "") = "" Then
            cTrackingInfo = RS2("TRACKING_INFO")
         Else
            cTrackingInfo = cTrackingInfo  & "," & RS2("TRACKING_INFO")
         End If
      End If
   rs2.MoveNext
   Loop
   RS2.CLOSE

Open in new window


Any help would be greatly appreciated.
BFanguyAsked:
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.

Russell FoxDatabase DeveloperCommented:
In 2008 your best bet is to use XML. This example takes a list of all databases on your server and puts their names into a single, comma-separated string:
SELECT	STUFF( 
     (SELECT	', ' + name
	  FROM		sys.databases
	  WHERE		database_id > 4
	  ORDER BY	name
		FOR	  XML PATH('') ,
				  ROOT('MyString') ,
				  TYPE 
     ).value('/MyString[1]', 'varchar(max)'), 1, 2, '') AS namelist;

Open in new window

BFanguyAuthor Commented:
Thank you Russell,

this worked perfect:
SELECT	STUFF( 
     (SELECT	', ' + TRACKING_INFO
	  FROM		CT_PACKLIST_TRACKING_INFO
	  WHERE		PACKLIST_ID = 'DC181363'
	  ORDER BY	create_date
		FOR	  XML PATH('') ,
				  ROOT('MyString') ,
				  TYPE 
     ).value('/MyString[1]', 'varchar(max)'), 1, 2, '') AS namelist;

Open in new window


is there a better way in 2012 or 2016?
Mark WillsTopic AdvisorCommented:
Yep, in 2017 we have STRING_AGG : https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

When you mentioned VIEW in SQL Server I thought you meant an actual VIEW (as in creating a view) and came up with
CREATE VIEW VW_TRACKING_INFO AS
with CTE_TRACKING as
( select distinct packlist_id
  from CT_PACKLIST_TRACKING_INFO
) select packlist_id,stuff(tracking_Info,1,2,'') as Tracking_Information
  from CTE_TRACKING C
  cross apply (select ', '+tracking_info from CT_PACKLIST_TRACKING_INFO I where I.PACKLIST_ID = c.PACKLIST_ID for xml path('')) T(tracking_info)
GO      

-- to test our view... just select from it like a normal table

select * from VW_TRACKING_INFO

-- or params 

SELECT * FROM VW_TRACKING_INFO WHERE PACKLIST_ID = 1

Open in new window

But would also argue that maybe a user defined function might be advantageous
CREATE FUNCTION UDF_TRACKING_INFO (@PID int = NULL)
returns varchar(max)
AS
begin
return ( select stuff(tracking_Info,1,2,'') as Tracking_Information
          from ( select distinct c.packlist_id, t.tracking_info
                 from CT_PACKLIST_TRACKING_INFO C
				 cross apply (select ', '+tracking_info from CT_PACKLIST_TRACKING_INFO I where I.PACKLIST_ID = c.PACKLIST_ID for xml path('')) T(tracking_info)
		         where c.PACKLIST_ID = @PID ) sq
      )
end
GO      

-- now to test for PACKING_ID = 1

select dbo.udf_tracking_info(111) as tracking_info

Open in new window

the CREATE view or function is a once off process. Simply refer to the view name or the function name...

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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

BFanguyAuthor Commented:
spoke to soon, unfortunately every time i try to past this into the view i wanted to add this to SSMS crashes

SELECT	STUFF((SELECT	', ' + TRACKING_INFO FROM pti WHERE pti.PACKLIST_ID = s.packlist_id ORDER BY create_date FOR XML PATH('') , ROOT('MyString') , TYPE ).value('/MyString[1]', 'varchar(max)'), 1, 2, '')

Open in new window


Can you look at the attached word document for what i am trying to do?  Thank you
tracking.doc
BFanguyAuthor Commented:
Looks like Mark already hit the nail on the head.  exactly what i needed.  thank you guys appreciate it.
BFanguyAuthor Commented:
Thanks again!
Mark WillsTopic AdvisorCommented:
You might have noticed I also suggested a function.

it has the advantage if searching via order number or reference (ets)

example :
;with CTE_Packlist as
( select order_id, reference, packlist_id
  FROM [CT_PACKLIST_TRACKING_INFO]
  where PACKLIST_ID is not null
  group by order_id,reference,PACKLIST_ID
) select order_id,reference,packlist_id, dbo.udf_tracking_info(packlist_id) as tracking_info
  from CTE_Packlist

Open in new window

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
VB Script

From novice to tech pro — start learning today.