November 1, 2013 at 8:26 am
Consider the below T-SQL
CREATE TABLE Temp10(Source nvarchar(50),Target nvarchar(50),Property1 int,Property2 int,
Property3 int,Property4 decimal,Property5 nvarchar(100),Property6 nvarchar(50))
INSERT INTO Temp10 values('A','DEF',10,8,20,12.34,'Good','Bad')
INSERT INTO Temp10 values('A','GKL',2,14,0,20.4,'Bad','Good')
INSERT INTO Temp10 values('A','MNO',4,4,60,100.6,'Excellent','Good')
INSERT INTO Temp10 values('B','ABC',3,7,20,10.6,'Good','Good')
INSERT INTO Temp10 values('B','DEF',9,3,40,70.6,'Bad','Bad')
INSERT INTO Temp10 values('C','XYZ',10,7,85,30.6,'Excellent','Excellent')
I want the output as follow
NewSequence NewTarget Option1 Option2 Option3
Source A DEF GKL MNO
Property1 10 2 4
Property2 8 14 4
Property3 20 0 60
Property4 12 20 101
Property5 Good Bad Excellent
Property6 Bad Good Good
Source B ABC DEF
Property1 3 9
Property2 7 3
Property3 20 40
Property4 11 71
Property5 Good Bad
Property6 Good Bad
Source C XYZ
Property1 10
Property2 7
Property3 85
Property4 31
Property5 Excellent
Property6 Excellent
I have tried all the options.. which include pivot,unpivot, cross apply 🙁 .... nothing seems to work.... :angry:
Any help on this would be highly appreciated......
Thanks.
November 1, 2013 at 9:49 am
I'm not sure why are you doing this on SQL Server, it might be better on the front-end.
However, if you really need to format your output like this on SQL Server, this should do it.
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Source ORDER BY Target) rn
FROM #Temp10)
SELECT Newsequence,
CASE WHEN Newsequence = 'Source' THEN Source ELSE '' END Source,
MAX( CASE WHEN rn = 1 THEN Value ELSE '' END) Option1,
MAX( CASE WHEN rn = 2 THEN Value ELSE '' END) Option2,
MAX( CASE WHEN rn = 3 THEN Value ELSE '' END) Option3
FROM CTE
CROSS APPLY (VALUES('Source', CAST( Target AS nvarchar(100))),
('Property1', CAST( Property1 AS nvarchar(100))),
('Property2', CAST( Property2 AS nvarchar(100))),
('Property3', CAST( Property3 AS nvarchar(100))),
('Property4', CAST( Property4 AS nvarchar(100))),
('Property5', CAST( Property5 AS nvarchar(100))),
('Property6', CAST( Property6 AS nvarchar(100)))
)x(Newsequence, Value)
GROUP BY Newsequence,
Source
ORDER BY CTE.Source,
CASE WHEN Newsequence = 'Source' THEN 'a' ELSE Newsequence END;
November 3, 2013 at 10:30 am
Thank you very much Luis for the reply. Now i have being trying to work around your query since yesterday but it does not seem to be working.I would appreciate if you could help me with this.
In the actual scenarion i did not have only 3 distinct entries in source column. They can vary. I tried to accomodate that change in the following way
DECLARE @CNT INT
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Source ORDER BY Target) rn
FROM Temp10)
SELECT @CNT=COUNT(DISTINCT Source) FROM Temp10
SELECT
Newsequence,
CASE WHEN Newsequence = 'Source' THEN Source ELSE '' END Source,
MAX( CASE WHEN rn = @CNT then Value else '' end) 'option' + convert(nvarchar(5),@cnt)
--MAX( CASE WHEN rn = 1 THEN Value ELSE '' END) Option1,
--MAX( CASE WHEN rn = 2 THEN Value ELSE '' END) Option2,
--MAX( CASE WHEN rn = 3 THEN Value ELSE '' END) Option3
FROM CTE
CROSS APPLY (VALUES('Source', CAST( Target AS nvarchar(100))),
('Property1', CAST( Property1 AS nvarchar(100))),
('Property2', CAST( Property2 AS nvarchar(100))),
('Property3', CAST( Property3 AS nvarchar(100))),
('Property4', CAST( Property4 AS nvarchar(100))),
('Property5', CAST( Property5 AS nvarchar(100))),
('Property6', CAST( Property6 AS nvarchar(100)))
)x(Newsequence, Value)
GROUP BY Newsequence,
Source
ORDER BY CTE.Source,
CASE WHEN Newsequence = 'Source' THEN 'a' ELSE Newsequence END;
Which is shows syntax error. Could you please help me with this.
November 3, 2013 at 1:10 pm
I corrected your syntax. Don't forget to place semicolons at the end of your statements or you'll get another error with the with statement.
WITH data AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Source ORDER BY Target) rn
FROM temp10),
cnt as (
select COUNT(DISTINCT Source) as cnt from temp10
),
cte as (
select * from data, cnt
)
SELECT
Newsequence,
CASE WHEN Newsequence = 'Source' THEN Source ELSE '' END Source,
MAX( CASE WHEN rn = cnt then Value else '' end) + 'option' + max(convert(nvarchar(5),cnt)),
MAX( CASE WHEN rn = 1 THEN Value ELSE '' END) Option1,
MAX( CASE WHEN rn = 2 THEN Value ELSE '' END) Option2,
MAX( CASE WHEN rn = 3 THEN Value ELSE '' END) Option3
FROM CTE
CROSS APPLY (VALUES('Source', CAST( [Target] AS nvarchar(100))),
('Property1', CAST( Property1 AS nvarchar(100))),
('Property2', CAST( Property2 AS nvarchar(100))),
('Property3', CAST( Property3 AS nvarchar(100))),
('Property4', CAST( Property4 AS nvarchar(100))),
('Property5', CAST( Property5 AS nvarchar(100))),
('Property6', CAST( Property6 AS nvarchar(100)))
)x(Newsequence, Value)
GROUP BY Newsequence,
Source
ORDER BY CTE.Source,
CASE WHEN Newsequence = 'Source' THEN 'a' ELSE Newsequence END;
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
November 3, 2013 at 11:20 pm
Thanks once again for the reply....
But what i am trying to do is, i want to loop through
MAX( CASE WHEN rn = 1 THEN Value ELSE '' END) Option1,
MAX( CASE WHEN rn = 2 THEN Value ELSE '' END) Option2,
MAX( CASE WHEN rn = 3 THEN Value ELSE '' END) Option3
For OptionN number of times where N is
select max(MaxNum)
from(
SELECT source,(count((Target))) as MaxNum
FROM Temp10
group by Source
)D
I tried using a CTE within a select which i guess is not premissible, is there any other way to go at it
November 4, 2013 at 8:12 am
In this article, you can find how to do it dynamic. But I still recommend to do all this formatting outside of SQL server.
November 4, 2013 at 9:31 am
What Luis is hinting at is that unless you write code to create the sql statement you want to run each time, the number of columns in your output will always be the same.
You have a choice, you can chose some maximum nunber of culumns you care about, and write your code to handle up-to that number of columns, or you can take the more-difficult route of dynamic sql as outlined in that article among others.
-a.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply