Solved

TSQL Query

Posted on 2015-01-13
8
89 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 44
SQL Query resolving a string conversion issue 26 37
Get Duration of last Status Update 4 29
Sql Query 4 14
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

20 Experts available now in Live!

Get 1:1 Help Now