Query Help

Question

Query Help

vijay.singh 14112

SSCommitted

Points: 1589
More actions
January 28, 2016 at 7:44 am

#324279

Hi Experts,
-->Below is my table and Data.
CREATE TABLE #Temp
(
IDBIGINT,
SDIDBIGINT,
CountofrecordsBIGINT,
CountOfProductsBIGINT,
CountOfPrdCodeBIGINT,
SumOfSalesBIGINT,
TotalUnitstoBesoldBIGINT,
NumberofnotNotSoldBIGINT,
NumberofItesmBIGINT
)
GO
INSERT #TEMP
SELECT 1175388162,27,49407,875,3299,316333.7000,0,0,0
UNION ALL SELECT 1175388162,26,49415,874,3272,326385.6500,0,0,0
UNION ALL SELECT 1175388162,25,49415,874,3283,319124.3800,0,0,0
UNION ALL SELECT 1175388162,24,49415,875,3259,333208.3800,0,0,0
UNION ALL SELECT 1175388162,23,49415,874,3277,323890.4600,0,0,0
UNION ALL SELECT 1175388162,22,49415,874,3277,328882.6200,0,0,0
UNION ALL SELECT 1175388162,21,49415,874,3278,324894.1600,0,0,0
UNION ALL SELECT 1175388162,20,49415,874,3293,271182.9400,0,0,0
UNION ALL SELECT 1175388162,19,49415,874,3287,265254.3800,0,0,0
UNION ALL SELECT 1175388162,18,49415,875,3281,284333.2300,0,0,0
UNION ALL SELECT 1175388162,17,49415,873,3312,291581.3000,0,0,0
UNION ALL SELECT 1175388162,16,49415,874,3283,302919.5200,0,0,0
UNION ALL SELECT 1175388162,15,49415,874,3287,310478.4900,0,0,0
UNION ALL SELECT 1175388162,14,49415,874,3288,312989.6800,0,0,0
UNION ALL SELECT 1175388162,13,49415,875,3282,314695.8600,0,0,0
UNION ALL SELECT 1175388162,12,49415,874,3180,385641.0200,0,0,0
UNION ALL SELECT 1175388162,11,49415,874,3261,321504.3200,0,0,0
UNION ALL SELECT 1175388162,10,49415,874,3293,281396.0400,0,0,0
--> This is how my data look like
SELECT * FROM #Temp
I want to display it like below for my report. Please help me writing the query for this. Thanks in advance.
ID,ID,ID,ID,ID,ID,ID
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SDIDCountofrecordsCountOfProductsCountOfPrdCodeSumOfSalesTotalUnitstoBesold umberofnotNotSold
Regards,
Vijay

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

vijay.singh 14112 (1/28/2016)
Hi Experts,
-->Below is my table and Data.
CREATE TABLE #Temp
(
IDBIGINT,
SDIDBIGINT,
CountofrecordsBIGINT,
CountOfProductsBIGINT,
CountOfPrdCodeBIGINT,
SumOfSalesBIGINT,
TotalUnitstoBesoldBIGINT,
NumberofnotNotSoldBIGINT,
NumberofItesmBIGINT
)
GO
INSERT #TEMP
SELECT 1175388162,27,49407,875,3299,316333.7000,0,0,0
UNION ALL SELECT 1175388162,26,49415,874,3272,326385.6500,0,0,0
UNION ALL SELECT 1175388162,25,49415,874,3283,319124.3800,0,0,0
UNION ALL SELECT 1175388162,24,49415,875,3259,333208.3800,0,0,0
UNION ALL SELECT 1175388162,23,49415,874,3277,323890.4600,0,0,0
UNION ALL SELECT 1175388162,22,49415,874,3277,328882.6200,0,0,0
UNION ALL SELECT 1175388162,21,49415,874,3278,324894.1600,0,0,0
UNION ALL SELECT 1175388162,20,49415,874,3293,271182.9400,0,0,0
UNION ALL SELECT 1175388162,19,49415,874,3287,265254.3800,0,0,0
UNION ALL SELECT 1175388162,18,49415,875,3281,284333.2300,0,0,0
UNION ALL SELECT 1175388162,17,49415,873,3312,291581.3000,0,0,0
UNION ALL SELECT 1175388162,16,49415,874,3283,302919.5200,0,0,0
UNION ALL SELECT 1175388162,15,49415,874,3287,310478.4900,0,0,0
UNION ALL SELECT 1175388162,14,49415,874,3288,312989.6800,0,0,0
UNION ALL SELECT 1175388162,13,49415,875,3282,314695.8600,0,0,0
UNION ALL SELECT 1175388162,12,49415,874,3180,385641.0200,0,0,0
UNION ALL SELECT 1175388162,11,49415,874,3261,321504.3200,0,0,0
UNION ALL SELECT 1175388162,10,49415,874,3293,281396.0400,0,0,0
--> This is how my data look like
SELECT * FROM #Temp
I want to display it like below for my report. Please help me writing the query for this. Thanks in advance.
ID,ID,ID,ID,ID,ID,ID
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SDIDCountofrecordsCountOfProductsCountOfPrdCodeSumOfSalesTotalUnitstoBesold umberofnotNotSold
Regards,
Vijay

Thanks for posting ddl and sample data. That is a great start. Can you explain the output? It doesn't make any sense to me at all. Based on your sample data what exactly should the output be.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vijay.singh 14112 SSCommitted Points: 1589 More actions · Answer 2

Hi,

Thanks the quick Reply.

As My Table Structure, all are columns.

I Want ID to be pivoted and rest of the column value should also be pivoted accordingly.

Some_Derived_Column nameIDIDIDID

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Value OF SDIDValue OF CountofrecordsValue OF CountOfProductsValue OF CountOfPrdCodeValue Of SumOfSales

100 9888769876 0987890 987687 8787556

101 9888769876 0987890 987687 8787556

And so on.

Regards,

Vijay

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

vijay.singh 14112 (1/28/2016)
Hi,
Thanks the quick Reply.
As My Table Structure, all are columns.
I Want ID to be pivoted and rest of the column value should also be pivoted accordingly.
Some_Derived_Column nameIDIDIDID
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Value OF SDIDValue OF CountofrecordsValue OF CountOfProductsValue OF CountOfPrdCodeValue Of SumOfSales
100 9888769876 0987890 987687 8787556
100 9888769876 0987890 987687 8787556
100 9888769876 0987890 987687 8787556
101 9888769876 0987890 987687 8787556
101 9888769876 0987890 987687 8787556
101 9888769876 0987890 987687 8787556
And so on.
Regards,
Vijay

I am sure this make sense to you but I have no idea what you want. The sample output you posted here doesn't seem to have any bearing on the sample data post the first time. In fact, most of your example output seems to just be the same value over and over. I am sure the query for this is not too difficult but I just have no idea what you are trying to do here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

How about if you create and post a temp table of the values and columns you expect as output and I can help you with the query to get there.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vijay.singh 14112 SSCommitted Points: 1589 More actions · Answer 5

Hi,

I have made a sample of result set in attached xlxs as well as in below script but the values are dummy. Please get back to me if anything required. Thanks for the help.

-->Below is my table and Data.

CREATE TABLE #Temp

(

IDBIGINT,

SDIDBIGINT,

CountofrecordsBIGINT,

CountOfProductsBIGINT,

CountOfPrdCodeBIGINT,

SumOfSalesBIGINT,

TotalUnitstoBesoldBIGINT,

NumberofnotNotSoldBIGINT,

NumberofItesmBIGINT

)

GO

INSERT #TEMP

SELECT 1175388162,27,49407,875,3299,316333.7000,0,0,0

UNION ALL SELECT 1175388162,26,49415,874,3272,326385.6500,0,0,0

UNION ALL SELECT 1175388162,25,49415,874,3283,319124.3800,0,0,0

UNION ALL SELECT 1175388162,24,49415,875,3259,333208.3800,0,0,0

UNION ALL SELECT 1175388162,23,49415,874,3277,323890.4600,0,0,0

UNION ALL SELECT 1175388162,22,49415,874,3277,328882.6200,0,0,0

UNION ALL SELECT 1175388162,21,49415,874,3278,324894.1600,0,0,0

UNION ALL SELECT 1175388162,20,49415,874,3293,271182.9400,0,0,0

UNION ALL SELECT 1175388162,19,49415,874,3287,265254.3800,0,0,0

UNION ALL SELECT 1175388162,18,49415,875,3281,284333.2300,0,0,0

UNION ALL SELECT 1175388162,17,49415,873,3312,291581.3000,0,0,0

UNION ALL SELECT 1175388162,16,49415,874,3283,302919.5200,0,0,0

UNION ALL SELECT 1175388162,15,49415,874,3287,310478.4900,0,0,0

UNION ALL SELECT 1175388162,14,49415,874,3288,312989.6800,0,0,0

UNION ALL SELECT 1175388162,13,49415,875,3282,314695.8600,0,0,0

UNION ALL SELECT 1175388162,12,49415,874,3180,385641.0200,0,0,0

UNION ALL SELECT 1175388162,11,49415,874,3261,321504.3200,0,0,0

UNION ALL SELECT 1175388162,10,49415,874,3293,281396.0400,0,0,0

--> This is how my data look like

CREATE Table #Temp1

(

DerivedColumnForSDI BIGINT --Value Of SDID

,[1175388162] BIGINT--ID holds the value of Countofrecords

,[1175388163] BIGINT--ID holds the value of CountOfProducts

,[1175388164] BIGINT--ID holds the value of CountOfPrdCode

,[1175388165] BIGINT--ID holds the value of SumOfSales

,[1175388166] BIGINT--ID holds the value of TotalUnitstoBesold

,[1175388167] BIGINT--ID holds the value of NumberofnotNotSold

,[1175388168] BIGINT--ID holds the value of NumberofItesm

)

-->

INSERT #Temp1

SELECT 98797,897,987686,987987,9878979,54535,34213,6987

UNION

SELECT 987,43543,965686,987987,9878979,54535,34213,2332

UNION

SELECT 987,43543,965686,987987,9878979,54535,34213,544

UNION

SELECT 987,43543,965686,987987,9878979,54535,34213,54343

SELECT * FROM #Temp1

Regards,

Vijay

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

vijay.singh 14112 (1/28/2016)
Hi,
I have made a sample of result set in attached xlxs as well as in below script but the values are dummy. Please get back to me if anything required. Thanks for the help.
-->Below is my table and Data.
CREATE TABLE #Temp
(
IDBIGINT,
SDIDBIGINT,
CountofrecordsBIGINT,
CountOfProductsBIGINT,
CountOfPrdCodeBIGINT,
SumOfSalesBIGINT,
TotalUnitstoBesoldBIGINT,
NumberofnotNotSoldBIGINT,
NumberofItesmBIGINT
)
GO
INSERT #TEMP
SELECT 1175388162,27,49407,875,3299,316333.7000,0,0,0
UNION ALL SELECT 1175388162,26,49415,874,3272,326385.6500,0,0,0
UNION ALL SELECT 1175388162,25,49415,874,3283,319124.3800,0,0,0
UNION ALL SELECT 1175388162,24,49415,875,3259,333208.3800,0,0,0
UNION ALL SELECT 1175388162,23,49415,874,3277,323890.4600,0,0,0
UNION ALL SELECT 1175388162,22,49415,874,3277,328882.6200,0,0,0
UNION ALL SELECT 1175388162,21,49415,874,3278,324894.1600,0,0,0
UNION ALL SELECT 1175388162,20,49415,874,3293,271182.9400,0,0,0
UNION ALL SELECT 1175388162,19,49415,874,3287,265254.3800,0,0,0
UNION ALL SELECT 1175388162,18,49415,875,3281,284333.2300,0,0,0
UNION ALL SELECT 1175388162,17,49415,873,3312,291581.3000,0,0,0
UNION ALL SELECT 1175388162,16,49415,874,3283,302919.5200,0,0,0
UNION ALL SELECT 1175388162,15,49415,874,3287,310478.4900,0,0,0
UNION ALL SELECT 1175388162,14,49415,874,3288,312989.6800,0,0,0
UNION ALL SELECT 1175388162,13,49415,875,3282,314695.8600,0,0,0
UNION ALL SELECT 1175388162,12,49415,874,3180,385641.0200,0,0,0
UNION ALL SELECT 1175388162,11,49415,874,3261,321504.3200,0,0,0
UNION ALL SELECT 1175388162,10,49415,874,3293,281396.0400,0,0,0
--> This is how my data look like
CREATE Table #Temp1
(
DerivedColumnForSDI BIGINT --Value Of SDID
,[1175388162] BIGINT--ID holds the value of Countofrecords
,[1175388163] BIGINT--ID holds the value of CountOfProducts
,[1175388164] BIGINT--ID holds the value of CountOfPrdCode
,[1175388165] BIGINT--ID holds the value of SumOfSales
,[1175388166] BIGINT--ID holds the value of TotalUnitstoBesold
,[1175388167] BIGINT--ID holds the value of NumberofnotNotSold
,[1175388168] BIGINT--ID holds the value of NumberofItesm
)
-->
INSERT #Temp1
SELECT 98797,897,987686,987987,9878979,54535,34213,6987
UNION
SELECT 987,43543,965686,987987,9878979,54535,34213,2332
UNION
SELECT 987,43543,965686,987987,9878979,54535,34213,544
UNION
SELECT 987,43543,965686,987987,9878979,54535,34213,54343
SELECT * FROM #Temp1
Regards,
Vijay

This doesn't make any sense at all. What is the logic behind these values? You have some sample data and now you have posted some expected output. How do you get those values? And your excel spreadsheet just doesn't make any sense to me at all.

Let's look at your columns. The ONLY value you have posted for ID is 1175388162. Where do all those other values come from? I will try one last time to ask you for information required to help you here. You have to realize I can't see your screen, I have no details about your requirements other than what you posted, I have no idea what your desired output it. As it stands right now this is an impossible to answer situation. I am thinking that maybe you want to do a cross tab and use the values from one of the columns as the column name??? But the input values and the desired output values are just random nonsense. Put yourself in my shoes and ask yourself if you think you could understand the question based on what you have posted.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/