Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.60/5 (3 votes)
i have below table :

ID   SalesID    Qt    unit
 1    1355      20    2500
 2    1355      20    5000
 3    1356      37    30000
 4    1356      37    20000
 5    1356      37    5000


Now i want to get query for below result

SalesID    Qt1   unit1    Qt2   unit2    Qt3   unit3
 1355      20    2500     20    5000
 1356      37    30000    37    20000    37    5000



I need to answer this question please help me
Posted
Comments
Maciej Los 23-Jul-13 16:36pm    
What have you tried? Where are you stuck?
aref.bozorgmehr 23-Jul-13 17:32pm    
i have a data base that's not mine
i want to send sms for each user
[no name] 23-Jul-13 17:47pm    
"i want to send sms for each user", so you actually want to do something that has absolutely nothing to do with your posted not-even-close-to-being-a-valid-question? Why don't you update your question to include an actual question and include something along the lines of what you really want to do?

Test it:
SQL
--declare destination table
DECLARE @data TABLE (ID INT IDENTITY(1,1), SalesID INT, Qt INT, unit INT)
--insert data
INSERT INTO @data (SalesID, Qt, unit)
SELECT 1355 AS SalesID, 20 AS Qt, 2500 AS unit
UNION ALL SELECT 1355, 20, 5000             
UNION ALL SELECT 1356, 37, 30000            
UNION ALL SELECT 1356, 37, 20000          
UNION ALL SELECT 1356, 37, 5000 

--declare temporary table to store RowNo
DECLARE @tmp TABLE (RowNo INT, ID INT, SalesID INT, Qt INT, unit INT)
--insert data
INSERT INTO @tmp (RowNo, SalesID, Qt, unit)
SELECT ROW_NUMBER() OVER(PARTITION BY Qt ORDER BY SalesID) AS RowNo, SalesID, Qt, unit
FROM @data

--pivot data
SELECT PT2.SalesID, SUM(PT4.[Qt1]) AS [Qt1], SUM(PT2.[unit1]) AS [unit1], SUM(PT4.[Qt2]) AS [Qt2], SUM(PT2.[unit2]) AS [unit2], 
		SUM(PT4.[Qt3]) AS [Qt3], SUM(PT2.[unit3]) AS [unit3]
FROM (
	SELECT RowNo, SalesID, [unit1], [unit2], [unit3]
	FROM (
		SELECT RowNo, SalesID, 'Qt' + CONVERT(VARCHAR(10), RowNo) AS QtDesc, Qt, 'unit' + CONVERT(VARCHAR(10), RowNo) AS UnitDesc, unit AS Unit
		FROM @tmp AS T
		) AS DT1
	PIVOT(MAX(Unit) FOR UnitDesc IN ([unit1], [unit2], [unit3])) AS PT1) AS PT2
LEFT JOIN (
	SELECT RowNo, SalesID, [Qt1], [Qt2], [Qt3]
	FROM ( 
		SELECT RowNo, SalesID, 'Qt' + CONVERT(VARCHAR(10), RowNo) AS QtDesc, Qt, 'unit' + CONVERT(VARCHAR(10), RowNo) AS UnitDesc, unit AS Unit
		FROM @tmp AS T
		) AS DT2
	PIVOT(MAX(Qt) FOR QtDesc IN ([Qt1], [Qt2], [Qt3])) AS PT3
	) AS PT4 ON PT2.RowNo = PT4.RowNo 
GROUP BY PT2.SalesID


Output:
1355    57  5000    57  10000   NULL    NULL
1356    57  60000   57  40000   37  5000
 
Share this answer
 
SQL Server:
SQL
SELECT TOP 1 SalesID FROM table
 
Share this answer
 
Comments
[no name] 23-Jul-13 16:25pm    
Not me but I think what he is really looking for is a pivot.
Jason Gleim 23-Jul-13 16:33pm    
Agh! You're right! I looked right over that!

Quick! Look! A new Royal Baby!
[no name] 23-Jul-13 18:11pm    
Ah well I would say never mind. Th OPs "solution" was to use C# code.... imagine that. I can't in good conscience upvote you offset the down vote as you did not really answer the question. On the other hand, his "question" had really nothing to do with what he asked.... If only there were some sort of really important current event going on that would distract me from these sort of quandaries..... like a royal baby or something.... :-)
Jason Gleim 23-Jul-13 16:33pm    
... jason runs from the room...
I find solution for this.

i use c# code .
 
Share this answer
 
Comments
[no name] 23-Jul-13 17:59pm    
No.... that is not a solution at all.
aref.bozorgmehr 23-Jul-13 19:10pm    
what can i do ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900