Solved

TSQL Query

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

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

 
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
 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

856 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