Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TSQL Query

Posted on 2015-01-13
8
Medium Priority
?
99 Views
Last Modified: 2015-01-13
How to write a TSQL to present records in a table as shown in the attached file ?

Tks
TSQL.xlsx
0
Comment
Question by:AXISHK
  • 4
  • 3
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40546162
You may need an AutoNumber to get it in the right order.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40546169
Here you go:

DECLARE @Sample TABLE
    (
      InventBatchID nvarchar(20),
	  ItemID nvarchar(20),
	  TypeID nvarchar(20),
	  SType int,
	  Result nvarchar(20))

INSERT  INTO @Sample
VALUES ('P000042545', '185XA00856', '14KC', 0, '12DFA0.06'),
('P000042545', '185XA00856', '14KC', 0, '6DFC0.17'),
('P000042545', '185XA00856', '14KC', 0, '1DFE0.09'),
('P000042545', '185XA00856', '14KC', 4, '2,83'),
('P000042545', '185XA00856', '14KC', 0, '8DFB0.10'),

('P000160279', '193XN02927', '18KT', 0, '19DFB0.32'),
('P000160279', '193XN02927', '18KT', 4, '17,21'),
('P000160279', '193XN02927', '18KT', 0, '105DFA0.57'),
('P000160279', '193XN02927', '18KT', 4, '1,08'),
('P000160279', '193XN02927', '18KT', 1, '6XAM3.71'),
('P000160279', '193XN02927', '18KT', 1, '3XBT3.13'),
('P000160279', '193XN02927', '18KT', 1, '3XCQ2.16'),
('P000160279', '193XN02927', '18KT', 1, '8XCT5.47'),
('P000160279', '193XN02927', '18KT', 1, '3XGA1.93'),
('P000160279', '193XN02927', '18KT', 1, '2XLB1.74'),
('P000160279', '193XN02927', '18KT', 1, '3XLQ1.47'),
('P000160279', '193XN02927', '18KT', 1, '8XSQ4.88'),

('P000241984', '123XD00160', 'CUW', 4, '1,78'),
('P000241984', '123XD00160', 'CUW', 1, '[TaT')


select InventBatchID, ItemID, TypeID, 
stuff((select ',' + Result from @Sample as T
where T.InventBatchID = S.InventBatchID and T.ItemID = S.ItemID and t.TypeID = s.TypeID and t.SType = s.SType
for xml path('')),1,1,'') as MyResult
from @Sample as S
group by InventBatchID, ItemID, TypeID, SType

Open in new window

0
 

Author Comment

by:AXISHK
ID: 40546186
Can you help me to understand the expression ? What does  the stuff and for xml path mean ?

Tks
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40546194
"Stuff" substitutes one expression for another. Here I am substituting the first character for nothing.

XML PATH exports the results into XML. It is a very big topic - this might help: http://msdn.microsoft.com/en-us/library/ms178107.aspx
0
 

Author Comment

by:AXISHK
ID: 40546447
Test the query, if i omit the 'for xml path('')', it doesn't work. What's its purpose in this scenario ? Why do I need to format the result into XML rather than rowset ?? Tks

Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

select InventBatchID, ItemID, TypeID,
(select ',' + Result from @Sample as T
where T.InventBatchID = S.InventBatchID and T.ItemID = S.ItemID and t.TypeID = s.TypeID and t.SType = s.SType) as MyResult
from @Sample as S
group by InventBatchID, ItemID, TypeID, SType
Tks
0
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40546490
Here is your solution...

 SELECT DISTINCT INVENTBATCHID, ITEMID, TYPEID,
 STUFF ((Select ' '+ RESULT From tempExe as p1
 WHERE  p2.INVENTBATCHID=p1.INVENTBATCHID And P2.ITEMID=p1.ITEMID And p2.TYPEID=p1.TYPEID And p2.STYPE=P1.STYPE
 For XML PATH('')),1,1,'') as RResult
 FROM tempExe as p2
 GROUP BY INVENTBATCHID, ITEMID, TYPEID, STYPE
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40546493
for xml path('') allows you to put all of the relevant results on one line.

If it works...
0
 

Author Closing Comment

by:AXISHK
ID: 40548099
Tks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question