Increment values by column into rows— SQL Server [closed]
0
I have the following type of data
CREATE TABLE #tmp
(
Room [NVARCHAR](50) NOT NULL,
iking INT,
iqueen INT,
isingle INT,
idouble INT
)
INSERT INTO #tmp
VALUES ('Marriot', 0, 1, 2, 1),
('Hilton', 1, 2, 0, 1)
I tried Cross Apply and case statements
I add data into temp table and wrote 4 cross apply functions for each column
King
SELECT tk.Room, tk.iking, Type = CONCAT('BED', t.n)
INTO #tempking1
FROM #tmp tk
CROSS APPLY
(SELECT TOP (tk.iking)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tk.Room;
--select * from #tempking1
Queen
SELECT
tq.Room, tq.iQueen,
Type = CASE WHEN ROOM in (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
ELSE CONCAT('BED', t.n)
END
INTO #tempQueen1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.iQueen)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempqueen1
Single
SELECT
tq.Room, tq.isingle,
Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1)
THEN CONCAT('BED', t.n + 1)
ElSE CONCAT('BED', t.n)
END
INTO #tempsingle1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.isingle)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempsingle1
Double
SELECT
tq.Room, tq.isingle,
Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempsingle1)
THEN CONCAT('BED', t.n + 1)
ELSE CONCAT('BED', t.n)
END
INTO #tempdouble1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.isingle)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempDouble1
SELECT Room, Type, 'King' AS Descp FROM #tempKing1
UNION ALL
SELECT Room, Type, 'Queeen' AS Descp FROM #tempQueen1
UNION ALL
SELECT Room, Type, 'Single' AS Descp FROM #tempsingle1
UNION ALL
SELECT Room, Type, 'Double' AS Descp FROM #tempDouble1
but I got
My excepted output is
Could you please help me
sql
sql-server edited Nov 14 '18 at 20:41
marc_s
577k12911151260
asked Nov 14 '18 at 19:14
Venkata Jagadish PippallaVenkata Jagadish Pippalla
33
closed as off-topic by Sean Lange, Cindy Meister, marc_s, Patrick Mevzek, Shiladitya Nov 15 '18 at 4:53
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – Sean Lange, Cindy Meister, Shiladitya
If this question can be reworded to fit the rules in the help center, please edit the question.
|
show 1 more comment
0
I have the following type of data
CREATE TABLE #tmp
(
Room [NVARCHAR](50) NOT NULL,
iking INT,
iqueen INT,
isingle INT,
idouble INT
)
INSERT INTO #tmp
VALUES ('Marriot', 0, 1, 2, 1),
('Hilton', 1, 2, 0, 1)
I tried Cross Apply and case statements
I add data into temp table and wrote 4 cross apply functions for each column
King
SELECT tk.Room, tk.iking, Type = CONCAT('BED', t.n)
INTO #tempking1
FROM #tmp tk
CROSS APPLY
(SELECT TOP (tk.iking)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tk.Room;
--select * from #tempking1
Queen
SELECT
tq.Room, tq.iQueen,
Type = CASE WHEN ROOM in (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
ELSE CONCAT('BED', t.n)
END
INTO #tempQueen1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.iQueen)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempqueen1
Single
SELECT
tq.Room, tq.isingle,
Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1)
THEN CONCAT('BED', t.n + 1)
ElSE CONCAT('BED', t.n)
END
INTO #tempsingle1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.isingle)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempsingle1
Double
SELECT
tq.Room, tq.isingle,
Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempsingle1)
THEN CONCAT('BED', t.n + 1)
ELSE CONCAT('BED', t.n)
END
INTO #tempdouble1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.isingle)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempDouble1
SELECT Room, Type, 'King' AS Descp FROM #tempKing1
UNION ALL
SELECT Room, Type, 'Queeen' AS Descp FROM #tempQueen1
UNION ALL
SELECT Room, Type, 'Single' AS Descp FROM #tempsingle1
UNION ALL
SELECT Room, Type, 'Double' AS Descp FROM #tempDouble1
but I got
My excepted output is
Could you please help me
sql
sql-server edited Nov 14 '18 at 20:41
marc_s
577k12911151260
asked Nov 14 '18 at 19:14
Venkata Jagadish PippallaVenkata Jagadish Pippalla
33
closed as off-topic by Sean Lange, Cindy Meister, marc_s, Patrick Mevzek, Shiladitya Nov 15 '18 at 4:53
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – Sean Lange, Cindy Meister, Shiladitya
If this question can be reworded to fit the rules in the help center, please edit the question.
7
Hi and welcome to SO. Not many people are just going to write this for you. And posting images of data is not helpful, why? You need to post some details like this article.
– Sean Lange
Nov 14 '18 at 19:16
As commented, you will need to improve your question in order to get help. A good start would be to add the statements that you have tried. It also looks like you could potentially solve the problem with a SELECT DISTINCT statement
– mtr.web
Nov 14 '18 at 19:19
You say you tried usingCROSS APPLYand aCASEexpression (it's not a statement). Could you include the code you tried when you update your post as per Sean's guide lines too please? Seeing your attempts often helps us understand your goal better, and sometimes it's a very small change that gets you what you're after; making the answer far easier for others to provide.
– Larnu
Nov 14 '18 at 19:25
Please don't put your code in the comments. It is part of the question...not to mention the formatting is just horrible in comments.
– Sean Lange
Nov 14 '18 at 19:56
To clarify Sean's comment, EDIT your question and put this info into it. Prefix each line with 4 spaces to render it as a code block. Please ensure your sql is nicely formatted/pretty printed and not just one big block of unindented/unformatted mess
– Caius Jard
Nov 14 '18 at 20:01
|
show 1 more comment
0
0
0
I have the following type of data
CREATE TABLE #tmp
(
Room [NVARCHAR](50) NOT NULL,
iking INT,
iqueen INT,
isingle INT,
idouble INT
)
INSERT INTO #tmp
VALUES ('Marriot', 0, 1, 2, 1),
('Hilton', 1, 2, 0, 1)
I tried Cross Apply and case statements
I add data into temp table and wrote 4 cross apply functions for each column
King
SELECT tk.Room, tk.iking, Type = CONCAT('BED', t.n)
INTO #tempking1
FROM #tmp tk
CROSS APPLY
(SELECT TOP (tk.iking)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tk.Room;
--select * from #tempking1
Queen
SELECT
tq.Room, tq.iQueen,
Type = CASE WHEN ROOM in (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
ELSE CONCAT('BED', t.n)
END
INTO #tempQueen1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.iQueen)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempqueen1
Single
SELECT
tq.Room, tq.isingle,
Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1)
THEN CONCAT('BED', t.n + 1)
ElSE CONCAT('BED', t.n)
END
INTO #tempsingle1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.isingle)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempsingle1
Double
SELECT
tq.Room, tq.isingle,
Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempsingle1)
THEN CONCAT('BED', t.n + 1)
ELSE CONCAT('BED', t.n)
END
INTO #tempdouble1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.isingle)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempDouble1
SELECT Room, Type, 'King' AS Descp FROM #tempKing1
UNION ALL
SELECT Room, Type, 'Queeen' AS Descp FROM #tempQueen1
UNION ALL
SELECT Room, Type, 'Single' AS Descp FROM #tempsingle1
UNION ALL
SELECT Room, Type, 'Double' AS Descp FROM #tempDouble1
but I got
My excepted output is
Could you please help me
sql
sql-server edited Nov 14 '18 at 20:41
marc_s
577k12911151260
asked Nov 14 '18 at 19:14
Venkata Jagadish PippallaVenkata Jagadish Pippalla
33
I have the following type of data
CREATE TABLE #tmp
(
Room [NVARCHAR](50) NOT NULL,
iking INT,
iqueen INT,
isingle INT,
idouble INT
)
INSERT INTO #tmp
VALUES ('Marriot', 0, 1, 2, 1),
('Hilton', 1, 2, 0, 1)
I tried Cross Apply and case statements
I add data into temp table and wrote 4 cross apply functions for each column
King
SELECT tk.Room, tk.iking, Type = CONCAT('BED', t.n)
INTO #tempking1
FROM #tmp tk
CROSS APPLY
(SELECT TOP (tk.iking)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tk.Room;
--select * from #tempking1
Queen
SELECT
tq.Room, tq.iQueen,
Type = CASE WHEN ROOM in (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
ELSE CONCAT('BED', t.n)
END
INTO #tempQueen1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.iQueen)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempqueen1
Single
SELECT
tq.Room, tq.isingle,
Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1)
THEN CONCAT('BED', t.n + 1)
ElSE CONCAT('BED', t.n)
END
INTO #tempsingle1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.isingle)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempsingle1
Double
SELECT
tq.Room, tq.isingle,
Type = CASE WHEN ROOM IN (SELECT Distinct ROOM FROM #tempking1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempqueen1)
THEN CONCAT('BED', t.n + 1)
WHEN ROOM IN (SELECT Distinct ROOM FROM #tempsingle1)
THEN CONCAT('BED', t.n + 1)
ELSE CONCAT('BED', t.n)
END
INTO #tempdouble1
FROM #tmp tq
CROSS APPLY
(SELECT TOP (tq.isingle)
n = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o) t
ORDER BY tq.Room;
--select * from #tempDouble1
SELECT Room, Type, 'King' AS Descp FROM #tempKing1
UNION ALL
SELECT Room, Type, 'Queeen' AS Descp FROM #tempQueen1
UNION ALL
SELECT Room, Type, 'Single' AS Descp FROM #tempsingle1
UNION ALL
SELECT Room, Type, 'Double' AS Descp FROM #tempDouble1
but I got
My excepted output is
Could you please help me
sql
sql-server sql
sql-server edited Nov 14 '18 at 20:41
marc_s
577k12911151260
asked Nov 14 '18 at 19:14
Venkata Jagadish PippallaVenkata Jagadish Pippalla
33
edited Nov 14 '18 at 20:41
marc_s
577k12911151260
asked Nov 14 '18 at 19:14
Venkata Jagadish PippallaVenkata Jagadish Pippalla
33
edited Nov 14 '18 at 20:41
marc_s
577k12911151260
edited Nov 14 '18 at 20:41
marc_s
577k12911151260
edited Nov 14 '18 at 20:41
marc_s
577k12911151260
577k12911151260
asked Nov 14 '18 at 19:14
Venkata Jagadish PippallaVenkata Jagadish Pippalla
33
asked Nov 14 '18 at 19:14
Venkata Jagadish PippallaVenkata Jagadish Pippalla
33
asked Nov 14 '18 at 19:14
Venkata Jagadish PippallaVenkata Jagadish Pippalla
33
33
closed as off-topic by Sean Lange, Cindy Meister, marc_s, Patrick Mevzek, Shiladitya Nov 15 '18 at 4:53
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – Sean Lange, Cindy Meister, Shiladitya
If this question can be reworded to fit the rules in the help center, please edit the question.
closed as off-topic by Sean Lange, Cindy Meister, marc_s, Patrick Mevzek, Shiladitya Nov 15 '18 at 4:53
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – Sean Lange, Cindy Meister, Shiladitya
If this question can be reworded to fit the rules in the help center, please edit the question.
7
Hi and welcome to SO. Not many people are just going to write this for you. And posting images of data is not helpful, why? You need to post some details like this article.
– Sean Lange
Nov 14 '18 at 19:16
As commented, you will need to improve your question in order to get help. A good start would be to add the statements that you have tried. It also looks like you could potentially solve the problem with a SELECT DISTINCT statement
– mtr.web
Nov 14 '18 at 19:19
You say you tried usingCROSS APPLYand aCASEexpression (it's not a statement). Could you include the code you tried when you update your post as per Sean's guide lines too please? Seeing your attempts often helps us understand your goal better, and sometimes it's a very small change that gets you what you're after; making the answer far easier for others to provide.
– Larnu
Nov 14 '18 at 19:25
Please don't put your code in the comments. It is part of the question...not to mention the formatting is just horrible in comments.
– Sean Lange
Nov 14 '18 at 19:56
To clarify Sean's comment, EDIT your question and put this info into it. Prefix each line with 4 spaces to render it as a code block. Please ensure your sql is nicely formatted/pretty printed and not just one big block of unindented/unformatted mess
– Caius Jard
Nov 14 '18 at 20:01
|
show 1 more comment
7
Hi and welcome to SO. Not many people are just going to write this for you. And posting images of data is not helpful, why? You need to post some details like this article.
– Sean Lange
Nov 14 '18 at 19:16
As commented, you will need to improve your question in order to get help. A good start would be to add the statements that you have tried. It also looks like you could potentially solve the problem with a SELECT DISTINCT statement
– mtr.web
Nov 14 '18 at 19:19
You say you tried usingCROSS APPLYand aCASEexpression (it's not a statement). Could you include the code you tried when you update your post as per Sean's guide lines too please? Seeing your attempts often helps us understand your goal better, and sometimes it's a very small change that gets you what you're after; making the answer far easier for others to provide.
– Larnu
Nov 14 '18 at 19:25
Please don't put your code in the comments. It is part of the question...not to mention the formatting is just horrible in comments.
– Sean Lange
Nov 14 '18 at 19:56
To clarify Sean's comment, EDIT your question and put this info into it. Prefix each line with 4 spaces to render it as a code block. Please ensure your sql is nicely formatted/pretty printed and not just one big block of unindented/unformatted mess
– Caius Jard
Nov 14 '18 at 20:01
7
7
Hi and welcome to SO. Not many people are just going to write this for you. And posting images of data is not helpful, why? You need to post some details like this article.
– Sean Lange
Nov 14 '18 at 19:16
Hi and welcome to SO. Not many people are just going to write this for you. And posting images of data is not helpful, why? You need to post some details like this article.
– Sean Lange
Nov 14 '18 at 19:16
As commented, you will need to improve your question in order to get help. A good start would be to add the statements that you have tried. It also looks like you could potentially solve the problem with a SELECT DISTINCT statement
– mtr.web
Nov 14 '18 at 19:19
As commented, you will need to improve your question in order to get help. A good start would be to add the statements that you have tried. It also looks like you could potentially solve the problem with a SELECT DISTINCT statement
– mtr.web
Nov 14 '18 at 19:19
You say you tried using
CROSS APPLY and a CASE expression (it's not a statement). Could you include the code you tried when you update your post as per Sean's guide lines too please? Seeing your attempts often helps us understand your goal better, and sometimes it's a very small change that gets you what you're after; making the answer far easier for others to provide.– Larnu
Nov 14 '18 at 19:25
You say you tried using
CROSS APPLY and a CASE expression (it's not a statement). Could you include the code you tried when you update your post as per Sean's guide lines too please? Seeing your attempts often helps us understand your goal better, and sometimes it's a very small change that gets you what you're after; making the answer far easier for others to provide.– Larnu
Nov 14 '18 at 19:25
Please don't put your code in the comments. It is part of the question...not to mention the formatting is just horrible in comments.
– Sean Lange
Nov 14 '18 at 19:56
Please don't put your code in the comments. It is part of the question...not to mention the formatting is just horrible in comments.
– Sean Lange
Nov 14 '18 at 19:56
To clarify Sean's comment, EDIT your question and put this info into it. Prefix each line with 4 spaces to render it as a code block. Please ensure your sql is nicely formatted/pretty printed and not just one big block of unindented/unformatted mess
– Caius Jard
Nov 14 '18 at 20:01
To clarify Sean's comment, EDIT your question and put this info into it. Prefix each line with 4 spaces to render it as a code block. Please ensure your sql is nicely formatted/pretty printed and not just one big block of unindented/unformatted mess
– Caius Jard
Nov 14 '18 at 20:01
|
show 1 more comment
1 Answer
1
active
oldest
votes
0
You can use UNPIVOT operator to fix your solution. Try this script:
drop TABLE #tmp
go
drop TABLE #Hotel
go
CREATE TABLE #tmp
(
Room nvarchar(30) NOT NULL
,iking int
,iqueen int
,isingle int
,idouble int
)
CREATE TABLE #Hotel
(
Room NVARCHAR(30)
,RoomType NVARCHAR(30)
,Total INT
)
Insert into #tmp Values ('Marriot', 0,1,2,1),('Hilton', 1,2,0,1)
INSERT INTO #Hotel
SELECT Room, RoomType, Total
FROM
(SELECT Room,iking,iqueen,isingle,idouble
FROM #tmp) p
UNPIVOT
(Total FOR RoomType IN
(iking,iqueen,isingle,idouble)
)AS unpvt
SELECT Room
,RoomType
--,'Bed'+CAST(Number AS VARCHAR) AS [Desc]
,'Bed'+CAST(ROW_NUMBER() OVER(PARTITION BY Room ORDER BY RoomType,Number) AS VARCHAR) AS [Desc]
,'Bed'+CAST(Total AS varchar) [Desc2]
FROM #Hotel
INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0
answered Nov 14 '18 at 20:35
Zeki GumusZeki Gumus
1,402212
Thank you for your response We can use pivot, But if iqueen = 2 we need 2 rows and type must be bed1 and bed2. In your script it getting only one row iqueen and bed1
– Venkata Jagadish Pippalla
Nov 14 '18 at 20:53
I believe my script already cover it(with Numbers table). Did you execute it?
– Zeki Gumus
Nov 14 '18 at 20:56
I am sorry, I just noticed the desc detail. Still the script cover what you want.I have added [Desc2] to my final select script. Can you check it?
– Zeki Gumus
Nov 14 '18 at 21:00
Thank You Ayzek. Its working
– Venkata Jagadish Pippalla
Nov 14 '18 at 22:00
Ayzek, The code you provided is one-time use. If I re-run the code the bed+ value is incrementing and changing the values. Could you please explain me the "INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0"
– Venkata Jagadish Pippalla
Nov 15 '18 at 19:24
|
show 2 more comments
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You can use UNPIVOT operator to fix your solution. Try this script:
drop TABLE #tmp
go
drop TABLE #Hotel
go
CREATE TABLE #tmp
(
Room nvarchar(30) NOT NULL
,iking int
,iqueen int
,isingle int
,idouble int
)
CREATE TABLE #Hotel
(
Room NVARCHAR(30)
,RoomType NVARCHAR(30)
,Total INT
)
Insert into #tmp Values ('Marriot', 0,1,2,1),('Hilton', 1,2,0,1)
INSERT INTO #Hotel
SELECT Room, RoomType, Total
FROM
(SELECT Room,iking,iqueen,isingle,idouble
FROM #tmp) p
UNPIVOT
(Total FOR RoomType IN
(iking,iqueen,isingle,idouble)
)AS unpvt
SELECT Room
,RoomType
--,'Bed'+CAST(Number AS VARCHAR) AS [Desc]
,'Bed'+CAST(ROW_NUMBER() OVER(PARTITION BY Room ORDER BY RoomType,Number) AS VARCHAR) AS [Desc]
,'Bed'+CAST(Total AS varchar) [Desc2]
FROM #Hotel
INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0
answered Nov 14 '18 at 20:35
Zeki GumusZeki Gumus
1,402212
Thank you for your response We can use pivot, But if iqueen = 2 we need 2 rows and type must be bed1 and bed2. In your script it getting only one row iqueen and bed1
– Venkata Jagadish Pippalla
Nov 14 '18 at 20:53
I believe my script already cover it(with Numbers table). Did you execute it?
– Zeki Gumus
Nov 14 '18 at 20:56
I am sorry, I just noticed the desc detail. Still the script cover what you want.I have added [Desc2] to my final select script. Can you check it?
– Zeki Gumus
Nov 14 '18 at 21:00
Thank You Ayzek. Its working
– Venkata Jagadish Pippalla
Nov 14 '18 at 22:00
Ayzek, The code you provided is one-time use. If I re-run the code the bed+ value is incrementing and changing the values. Could you please explain me the "INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0"
– Venkata Jagadish Pippalla
Nov 15 '18 at 19:24
|
show 2 more comments
0
You can use UNPIVOT operator to fix your solution. Try this script:
drop TABLE #tmp
go
drop TABLE #Hotel
go
CREATE TABLE #tmp
(
Room nvarchar(30) NOT NULL
,iking int
,iqueen int
,isingle int
,idouble int
)
CREATE TABLE #Hotel
(
Room NVARCHAR(30)
,RoomType NVARCHAR(30)
,Total INT
)
Insert into #tmp Values ('Marriot', 0,1,2,1),('Hilton', 1,2,0,1)
INSERT INTO #Hotel
SELECT Room, RoomType, Total
FROM
(SELECT Room,iking,iqueen,isingle,idouble
FROM #tmp) p
UNPIVOT
(Total FOR RoomType IN
(iking,iqueen,isingle,idouble)
)AS unpvt
SELECT Room
,RoomType
--,'Bed'+CAST(Number AS VARCHAR) AS [Desc]
,'Bed'+CAST(ROW_NUMBER() OVER(PARTITION BY Room ORDER BY RoomType,Number) AS VARCHAR) AS [Desc]
,'Bed'+CAST(Total AS varchar) [Desc2]
FROM #Hotel
INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0
answered Nov 14 '18 at 20:35
Zeki GumusZeki Gumus
1,402212
Thank you for your response We can use pivot, But if iqueen = 2 we need 2 rows and type must be bed1 and bed2. In your script it getting only one row iqueen and bed1
– Venkata Jagadish Pippalla
Nov 14 '18 at 20:53
I believe my script already cover it(with Numbers table). Did you execute it?
– Zeki Gumus
Nov 14 '18 at 20:56
I am sorry, I just noticed the desc detail. Still the script cover what you want.I have added [Desc2] to my final select script. Can you check it?
– Zeki Gumus
Nov 14 '18 at 21:00
Thank You Ayzek. Its working
– Venkata Jagadish Pippalla
Nov 14 '18 at 22:00
Ayzek, The code you provided is one-time use. If I re-run the code the bed+ value is incrementing and changing the values. Could you please explain me the "INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0"
– Venkata Jagadish Pippalla
Nov 15 '18 at 19:24
|
show 2 more comments
0
0
0
You can use UNPIVOT operator to fix your solution. Try this script:
drop TABLE #tmp
go
drop TABLE #Hotel
go
CREATE TABLE #tmp
(
Room nvarchar(30) NOT NULL
,iking int
,iqueen int
,isingle int
,idouble int
)
CREATE TABLE #Hotel
(
Room NVARCHAR(30)
,RoomType NVARCHAR(30)
,Total INT
)
Insert into #tmp Values ('Marriot', 0,1,2,1),('Hilton', 1,2,0,1)
INSERT INTO #Hotel
SELECT Room, RoomType, Total
FROM
(SELECT Room,iking,iqueen,isingle,idouble
FROM #tmp) p
UNPIVOT
(Total FOR RoomType IN
(iking,iqueen,isingle,idouble)
)AS unpvt
SELECT Room
,RoomType
--,'Bed'+CAST(Number AS VARCHAR) AS [Desc]
,'Bed'+CAST(ROW_NUMBER() OVER(PARTITION BY Room ORDER BY RoomType,Number) AS VARCHAR) AS [Desc]
,'Bed'+CAST(Total AS varchar) [Desc2]
FROM #Hotel
INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0
answered Nov 14 '18 at 20:35
Zeki GumusZeki Gumus
1,402212
You can use UNPIVOT operator to fix your solution. Try this script:
drop TABLE #tmp
go
drop TABLE #Hotel
go
CREATE TABLE #tmp
(
Room nvarchar(30) NOT NULL
,iking int
,iqueen int
,isingle int
,idouble int
)
CREATE TABLE #Hotel
(
Room NVARCHAR(30)
,RoomType NVARCHAR(30)
,Total INT
)
Insert into #tmp Values ('Marriot', 0,1,2,1),('Hilton', 1,2,0,1)
INSERT INTO #Hotel
SELECT Room, RoomType, Total
FROM
(SELECT Room,iking,iqueen,isingle,idouble
FROM #tmp) p
UNPIVOT
(Total FOR RoomType IN
(iking,iqueen,isingle,idouble)
)AS unpvt
SELECT Room
,RoomType
--,'Bed'+CAST(Number AS VARCHAR) AS [Desc]
,'Bed'+CAST(ROW_NUMBER() OVER(PARTITION BY Room ORDER BY RoomType,Number) AS VARCHAR) AS [Desc]
,'Bed'+CAST(Total AS varchar) [Desc2]
FROM #Hotel
INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0
answered Nov 14 '18 at 20:35
Zeki GumusZeki Gumus
1,402212
edited Nov 15 '18 at 19:41
answered Nov 14 '18 at 20:35
Zeki GumusZeki Gumus
1,402212
answered Nov 14 '18 at 20:35
Zeki GumusZeki Gumus
1,402212
answered Nov 14 '18 at 20:35
Zeki GumusZeki Gumus
1,402212
1,402212
Thank you for your response We can use pivot, But if iqueen = 2 we need 2 rows and type must be bed1 and bed2. In your script it getting only one row iqueen and bed1
– Venkata Jagadish Pippalla
Nov 14 '18 at 20:53
I believe my script already cover it(with Numbers table). Did you execute it?
– Zeki Gumus
Nov 14 '18 at 20:56
I am sorry, I just noticed the desc detail. Still the script cover what you want.I have added [Desc2] to my final select script. Can you check it?
– Zeki Gumus
Nov 14 '18 at 21:00
Thank You Ayzek. Its working
– Venkata Jagadish Pippalla
Nov 14 '18 at 22:00
Ayzek, The code you provided is one-time use. If I re-run the code the bed+ value is incrementing and changing the values. Could you please explain me the "INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0"
– Venkata Jagadish Pippalla
Nov 15 '18 at 19:24
|
show 2 more comments
Thank you for your response We can use pivot, But if iqueen = 2 we need 2 rows and type must be bed1 and bed2. In your script it getting only one row iqueen and bed1
– Venkata Jagadish Pippalla
Nov 14 '18 at 20:53
I believe my script already cover it(with Numbers table). Did you execute it?
– Zeki Gumus
Nov 14 '18 at 20:56
I am sorry, I just noticed the desc detail. Still the script cover what you want.I have added [Desc2] to my final select script. Can you check it?
– Zeki Gumus
Nov 14 '18 at 21:00
Thank You Ayzek. Its working
– Venkata Jagadish Pippalla
Nov 14 '18 at 22:00
Ayzek, The code you provided is one-time use. If I re-run the code the bed+ value is incrementing and changing the values. Could you please explain me the "INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0"
– Venkata Jagadish Pippalla
Nov 15 '18 at 19:24
Thank you for your response We can use pivot, But if iqueen = 2 we need 2 rows and type must be bed1 and bed2. In your script it getting only one row iqueen and bed1
– Venkata Jagadish Pippalla
Nov 14 '18 at 20:53
Thank you for your response We can use pivot, But if iqueen = 2 we need 2 rows and type must be bed1 and bed2. In your script it getting only one row iqueen and bed1
– Venkata Jagadish Pippalla
Nov 14 '18 at 20:53
I believe my script already cover it(with Numbers table). Did you execute it?
– Zeki Gumus
Nov 14 '18 at 20:56
I believe my script already cover it(with Numbers table). Did you execute it?
– Zeki Gumus
Nov 14 '18 at 20:56
I am sorry, I just noticed the desc detail. Still the script cover what you want.I have added [Desc2] to my final select script. Can you check it?
– Zeki Gumus
Nov 14 '18 at 21:00
I am sorry, I just noticed the desc detail. Still the script cover what you want.I have added [Desc2] to my final select script. Can you check it?
– Zeki Gumus
Nov 14 '18 at 21:00
Thank You Ayzek. Its working
– Venkata Jagadish Pippalla
Nov 14 '18 at 22:00
Thank You Ayzek. Its working
– Venkata Jagadish Pippalla
Nov 14 '18 at 22:00
Ayzek, The code you provided is one-time use. If I re-run the code the bed+ value is incrementing and changing the values. Could you please explain me the "INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0"
– Venkata Jagadish Pippalla
Nov 15 '18 at 19:24
Ayzek, The code you provided is one-time use. If I re-run the code the bed+ value is incrementing and changing the values. Could you please explain me the "INNER JOIN master.dbo.spt_values N ON Total>=N.Number AND N.type='P' AND number<>0"
– Venkata Jagadish Pippalla
Nov 15 '18 at 19:24
|
show 2 more comments
7
Hi and welcome to SO. Not many people are just going to write this for you. And posting images of data is not helpful, why? You need to post some details like this article.
– Sean Lange
Nov 14 '18 at 19:16
As commented, you will need to improve your question in order to get help. A good start would be to add the statements that you have tried. It also looks like you could potentially solve the problem with a SELECT DISTINCT statement
– mtr.web
Nov 14 '18 at 19:19
You say you tried using
CROSS APPLYand aCASEexpression (it's not a statement). Could you include the code you tried when you update your post as per Sean's guide lines too please? Seeing your attempts often helps us understand your goal better, and sometimes it's a very small change that gets you what you're after; making the answer far easier for others to provide.– Larnu
Nov 14 '18 at 19:25
Please don't put your code in the comments. It is part of the question...not to mention the formatting is just horrible in comments.
– Sean Lange
Nov 14 '18 at 19:56
To clarify Sean's comment, EDIT your question and put this info into it. Prefix each line with 4 spaces to render it as a code block. Please ensure your sql is nicely formatted/pretty printed and not just one big block of unindented/unformatted mess
– Caius Jard
Nov 14 '18 at 20:01