Solved

TSQL Query

Posted on 2015-01-13
8
88 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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now